Proc sql to populate table

sq96 — Fri, 30 Aug 2019 09:00:40 GMT

I have two tables : T1 and T2 As below-- T1:

ID | Age
1  | 22
2  | 23
3  | 24

T2: 22, 23, 24 are column names.

ID | 22 | 23 |24
1  | 10 | 50 | 120
2  | 20 | 60 | 130
3  | 30 | 70 | 220

Now I need to create a third table as : Based on Age and ID of table 1, value in column is to be selected and column Dev is to be populated. Here column selection is done based of the row value of age in table 1.

Therefore answer would be:

ID | Age| Dev
1  | 22 | 10 
2  | 23 | 60 
3  | 24 | 220

For More Understanding:

You see T1, ID = 1 has Age = 22
Cross reference T2,
where Row = 1(ID from T1)
and Column = 22 (Age from T2),
therefore answer = 10.

Re: Proc sql to populate table

Shmuel — Fri, 30 Aug 2019 09:33:54 GMT

1) Sas variable names like 22 23 .. are sas invalid names. You probably imported those tables from excel.

I believe that by importing the variables will be var1 var2 .. or alike. You may need to rename those variables.

In order to display my solution I shall use names A22 A23 .. which are sas valid names.

2) possible solution:
Step 1 transpose table from wide to long:

proc transpose data=T2  out=temp;
   by ID AGE;
   name = 'DEV';
   var A22 - A24;    /* first age up to last age */
run;
proc sort data=temp; by ID AGE; run;

thus will result into table like:

ID    AGE   DEV
1      22   10
1      22   20
1      22   30
2      23   50
2      23   60
2      23   70
...

Next step will be to merge table T1 with the temp table by ID and AGE:

data want;
  merge T1  temp;
by ID AGE;
run;

It is not tested. In case of issues post the log.

topic Proc sql to populate table in SAS Procedures

Proc sql to populate table

Re: Proc sql to populate table