change the values to variable names

[nickspencer]

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

[ChrisNZ]

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.

[japelin]

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;

[andreas_lds]

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;

[Kurt_Bremser]

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;