CODEWITHSUNDEEP
sassas-macro
mymymine
mymymine

Reputation: 49

SAS/ Long to Wide with missing data using data step

I want converted my data from long to wide format using data step. The problem is that due to missing values the values are not placed in the correct cells. I think to solve the problem I have to include placeholder for missing values.

The problem is I don't know how to do. Can someone please give me tip on how to go about it.

data tic;

input id country$ month math;

datalines;
1 uk 1 10 
1 uk 2 15 
1 uk 3 24 
2 us 2 15 
2 us 4 12 
3 fl 1 15 
3 fl 2 16 
3 fl 3 17 
3 fl 4 15 
;
run;

proc sort data=tic;
by id;
run;


 data tot(drop=month math);
 retain month1-month4 math1-math4;
 array tat{4} month1-month4;
 array kat{4} math1-math4;
 set tic;

 by id;

 if first.id then do;
    i=1;
    do j=1 to 4;
       tat{j}=.;
       kat{j}=.;
    end;
end;

tat(i)=month;
kat(i)=math;

if last.id then output;

i+1;

run;

Edit I finally figured out what the problem is:

changed this lines of code 

tat(i)=month;
kat(i)=math;

to:

tat(month)=month;
kat(month)=math;

and it fixed the problem.

Upvotes: 0

Views: 366

Answers (1)

Richard
Richard

Reputation: 27508

Data transformations from tall and skinny to short and wide often mean that categorical data ends up as column names. This is a process of moving data to metadata, which can be a problem later on for dealing with BY or CLASS groups.

SAS has Proc TABULATE and Proc REPORT for creating pivoted output. Proc TRANSPOSE is also a good standard way of creating pivoted data.

I did notice that you are pivoting two columns at once. TRANSPOSE can't multi-pivot. The DATA Step approach you showed is a typical way for doing a transpose transform when the indices lie within known ranges. In your case the array declaration must be such that 'direct-addressing' via index can to handle the minimal and maximal month values that occur over all the data.

Upvotes: 0

Related Questions