SAS joining table and creating multiple columns based on data from other columns individually-CodePudding

Good Morning,

Sorry I am completely self taught so am probably missing something simple, I am trying to create a table based on values from other tables. I am not sure how to best explain what I want so here is the example;

Table1

Name   Lname   Issue1 Issue2  Issue3
Tom    Smith   1234   1258    1175
Dick   Scott   1258   1158    1852
Jane   Davis   1234   1385    1111
Sarah  Bennet  1158   1672    1234

Table2

Issue  Desc
1234   A
1258   B
1175   C
1158   D
1852   E
1385   F
1111   G
1672   H
1468   I

Want

Name   Lname   Issue1 Desc1 Issue2 Desc2  Issue3  Desc3
Tom    Smith   1234   A     1258   B      1175    C
Dick   Scott   1258   B     1158   D      1852    E
Jane   Davis   1234   A     1385   F      1111    G
Sarah  Bennet  1158   D     1672   H      1234    A

I have done this previously by doing multiple joins to a single table but it seems like there should be a better way, here is what I am currently using

Proc SQL;
Select
a.Name
a.Lname
a.Issue1
b.Desc as Desc1 
a.Issue2
c.Desc as Desc2 
a.Issue3
d.Desc as Desc3 

From work.Table1 a

Left Join work.Table2 b
on a.Issue1 eq b.Desc

Left Join work.Table2 c
on a.Issue2 eq c.Desc

Left Join work.Table2 d
on a.Issue3 eq d.Desc

So basically I want a table that has data from both but need multiple descriptions from Table 2 to match the issue values from table 1.

Thank you for your help!

CodePudding user response：

You should transpose your data from wide to long, as e. g. in this example using PROC TRANSPOSE. It is often better to have data in the "long" format, e. g. to use BY-grouping in statistical procedures.

First sort the BY-variables.

proc sort data=have;
  by Name Lname;
run;

Then transpose all variables Issue1-3.

proc transpose data=have out=want;
  by Name Lname;
  var Issue:;
run;

Then join with Table2.

CodePudding user response：

Create a format from Table 2
Use an Array in a data step to create the new columns in Table 1 if required, or apply format.

data issue_fmt;
set table2;
start=issue;
label=desc;
fmtname='$Issue_fmt';
type='C';
run;

proc format cntlin=issue_fmt;
run;

*apply format;
proc print data=table1 (obs=10);
var issue1-issue3;
format issue1-issue3 $issue_fmt.;
run;

*create new variable with format;

data want;
set have;

array issues(*) issue1-issue200;
array desc(200) desc1-desc200;

do i=1 to dim(issues);
  desc(i) = put(issues(i), $issue_fmt.);
end;

run;