Hello all,
I have a dateset Have. It has multiple records by identifier ID, all the values are same except for attrib1 and attrib2.
I want to change the attrib 1 values to column names and assign them the values of attrib2.
Have:
ID | Name | State | Date | attrib1 | attrib2 |
123 | John | AZ | 1/1/2021 | level | A |
123 | John | AZ | 1/2/2021 | Score | 120 |
123 | John | AZ | 1/3/2021 | Maint1 | 202 |
123 | John | AZ | 1/4/2021 | Maint3 | 501 |
123 | John | AZ | 1/5/2021 | Maint2 | 302 |
123 | John | AZ | 1/6/2021 | Maint4 | 101 |
234 | Mary | CA | 2/6/2021 | Maint2 | 150 |
234 | Mary | CA | 2/7/2021 | Maint1 | 101 |
Want:
ID | Name | State | Date | level | Maint1 | Maint2 | Maint3 | Maint4 | Score |
123 | John | AZ | 1/1/2021 | A | 202 | 302 | 501 | 101 | 120 |
234 | Mary | CA | 2/6/2021 | 101 | 150 |
What will be the best way to achieve this? Any suggestions or solution is appreciated.
Thanks,
Nick
Unless you are building a report, and not a data set, there is usually no good reason to do this. It can only make your life complicated.
Now if you have a good reason, look at proc transpose.
how about this code.
data Have;
input ID Name $ State $ Date:mmddyy10. attrib1 $ attrib2 $;
format date mmddyy10.;
datalines;
123 John AZ 1/1/2021 level A
123 John AZ 1/2/2021 Score 120
123 John AZ 1/3/2021 Maint1 202
123 John AZ 1/4/2021 Maint3 501
123 John AZ 1/5/2021 Maint2 302
123 John AZ 1/6/2021 Maint4 101
234 Mary CA 2/6/2021 Maint2 150
234 Mary CA 2/7/2021 Maint1 101
;
run;
proc sort data=have;
by id;
run;
data want;
set have;
by id;
length level $1 Maint1-Maint4 score 8;
retain firstDate level Maint: score;
if first.id then do;
firstDate =date;
call missing(level);
call missing(Score);
call missing(of Maint1-Maint4);
end;
select (attrib1);
when('level') level =attrib2;
when('Score') Score =input(attrib2,best.);
when('Maint1') Maint1 =input(attrib2,best.);
when('Maint2') Maint2 =input(attrib2,best.);
when('Maint3') Maint3 =input(attrib2,best.);
when('Maint4') Maint4 =input(attrib2,best.);
otherwise;
end;
if last.id then do;
date=firstDate;
output;
end;
drop firstDate attrib:;
run;
Fully agree to @ChrisNZ : the structure you want is hardly useful, except for a very, very low number of cases.
The following solution uses proc transpose:
data have;
length id 8 Name $ 8 State $ 2 Date 8 attrib1 $ 32 attrib2 $ 20;
informat Date mmddyy10.;
format date date9.;
input ID Name State Date attrib1 attrib2;
datalines;
123 John AZ 1/1/2021 Level A
123 John AZ 1/2/2021 Score 120
123 John AZ 1/3/2021 Maint1 202
123 John AZ 1/4/2021 Maint3 501
123 John AZ 1/5/2021 Maint2 302
123 John AZ 1/6/2021 Maint4 101
234 Mary CA 2/6/2021 Maint2 150
234 Mary CA 2/7/2021 Maint1 101
;
proc sort data=have out=sorted;
by id attrib1;
run;
proc transpose data=sorted out=transposed(drop= _name_);
by id;
var attrib2;
id attrib1;
run;
data want;
merge sorted(keep= ID Name State Date) transposed;
by ID;
if first.ID;
run;
For a report, use this:
data have;
input ID :$3. Name $ State :$2. Date :mmddyy10. attrib1 $ attrib2 $;
format date yymmdd10.;
datalines;
123 John AZ 1/1/2021 level A
123 John AZ 1/2/2021 Score 120
123 John AZ 1/3/2021 Maint1 202
123 John AZ 1/4/2021 Maint3 501
123 John AZ 1/5/2021 Maint2 302
123 John AZ 1/6/2021 Maint4 101
234 Mary CA 2/6/2021 Maint2 150
234 Mary CA 2/7/2021 Maint1 101
;
proc report data=have;
column id name state date attrib2,attrib1 n;
define id /group;
define name /group;
define state /group;
define date / analysis min;
define attrib2 / "" display;
define attrib1 / "" across;
define n / noprint;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.