02-05-2017 05:08 PM
I have a data set like below (input) and need your help in getting the output dataset.
input ColumnA $ ColumnB $ ColumnC $ ColumnD$ Value;
A B C A1 1
A B C A1 2
A B C A1 3
A B C A1 4
A B C A2 1
A B C A2 2
A B C A2 3
A B C A2 4
input ColumnA $ ColumnB $ ColumnC $ A1 A2;
A B C 1 1
A B C 2 2
A B C 3 3
A B C 4 4
02-05-2017 05:20 PM - edited 02-05-2017 05:23 PM
YOu have not sufficiently described the rules by which you want to associate the data records.
Please try to avoid mindreading requirements.
02-05-2017 05:40 PM
Thanks for your response.
1.The total number of observations should be devided into 2 groups, which is based on the values of the coulmnD(A1 and A2)
2. The observations should be grouped based on ColumnA,columnB, ColumnC and columnD
3. The Values in the columnD should become new coulmns, for example in the above example we have two different values A1 and A2 so we should get 2 new columns in the output called A1 and A2
3. Column A1 and A2 no need to have same rows. The total number of observation are going to be equal to the higher number of observations in any group. For example if group A1 contains 5 and A2 contains 3, so total number of obs will be equal to 5 and column A2 will have values 0.
Hope this is clear
02-05-2017 06:10 PM
You would have to create new variables to account for what you want. Given your example you could use something like:
data input; input ColumnA $ ColumnB $ ColumnC $ ColumnD $ Value; cards; A B C A1 1 A B C A1 2 A B C A1 3 A B C A1 4 A B C A2 1 A B C A2 2 A B C A2 3 A B C A2 4 ; data need; set input; if mod(_n_,4) eq 0 then x=4; else x=mod(_n_,4); run; proc sort data=need; by ColumnA ColumnB ColumnC x ColumnD; run; data need; set need; if mod(_n_,2) eq 1 then group+1; run; proc transpose data=need out=want (drop= group _name_); by ColumnA ColumnB ColumnC Group; var value; id ColumnD; run;
Art, CEO, AnalystFinder.com
02-06-2017 12:55 AM
Assuming (1) your data are sorted by columna/columnb/columnc and (2) columnd='A1' or 'A2', then you can use a MERGE process as below:
data want (drop=n n1 n2 columnd); n1=0; n2=0; do until (last.columnc); set input (keep=columna columnb columnc columnd); by columna columnb columnc; if columnd='A1' then n1=n1+1; else if columnd='A2' then n2=n2+1; end; do N=1 by 1 until (last.columnc); merge input (in=in1 where=(columnd='A1') rename=(value=a1)) input (in=in2 where=(columnd='A2') rename=(value=a2)); by columna columnb columnc; if n>n1 then a1=0; if n>n2 then a2=0; output; end; run;
Note that N1 and N2 are the counts of A1 and A2 records respectively.