topic Re: Add dummy columns in transposed dataset - sas in SAS Programming

Add dummy columns in transposed dataset - sas

melligeri — Tue, 19 Apr 2022 09:07:17 GMT

I have transposed data set

ID        PA_1        PA_2
-------------------------------   
1        user1      user2          
2        user3      user4           
3        user5      'NULL'         
4        user6      user7

Target data set

ID        PA_1        PA_2        PA_2        PA_2        PA_2        PA_2
---------------------------------------------------------------------------      
1        
2        
3        
4

Since the first table is transposed the columns created is dynamic, sometimes it have have PA_1,PA_2 sometimes it can have PA_1,PA_2,PA_3,PA_4,PA_5,PA_6

When I try to join the both the tables using the ID, since there is no other columns other than PA_1 and PA_2, the query fails.

Need a solution to this problem. Thanks.

Re: Add dummy columns in transposed dataset - sas

andreas_lds — Tue, 19 Apr 2022 10:32:46 GMT

Please fix the names in the target dataset, a dataset can't have multiple variable with the same name. And while you are at it, fill the result table with the values from "have"-dataset and post the code you are using to transpose.

Re: Add dummy columns in transposed dataset - sas

Tom — Tue, 19 Apr 2022 14:01:33 GMT

Your description is incomplete. Your sketches of the data is also incomplete since the second dataset has invalid names and no example values. Also it does not look like what you would get from transposing the first dataset.

Sounds like you hare transposing data with unknown number of repetitions and so an unknown number of variables are created. That is to be expected.

But the second issue is very unclear. Why would it matter to a merge whether the dataset has 2 or 10 extra variables? A typical merge will look like:

data want;
  merge a b ;
  by id;
run;

As long as A and B have the variable ID the merge will work.

Re: Add dummy columns in transposed dataset - sas

ErikLund_Jensen — Tue, 19 Apr 2022 15:47:48 GMT

Hi @melligeri

I would use something like the following code. I am not sure about the value "'NULL'" for ID 2, does it represent a missing value or does the transposed data set actually contain the quoted literal value? - In my example code I have understood it as "missing".

data xpose;
  ID = 1; PA_1 = 'user1'; PA_2 = 'user2'; output;
  ID = 2; PA_1 = 'user3'; PA_2 = 'user4'; output;
  ID = 3; PA_1 = 'user5'; PA_2 = ''; output;
  ID = 4; PA_1 = 'user6'; PA_2 = 'user7'; output;
run;

data master;
  length ID 8 PA_1-PA_6 $20;
  stop;
run;

data target;
  set master xpose;
run;

Result: