topic Re: Transpose Observations to Columns and add Flag in SAS Programming

Transpose Observations to Columns and add Flag

anandrc — Thu, 27 Mar 2025 10:20:06 GMT

Dear Community,

I have the following dataset. I would like to have my output dataset to be such a way that the unique observations(location) should become my columns and require a count column to suggest the no of values. Appreciate your inputs.

ID	Location
101	UK
101	France
101	Other
102	UK
102	Spain
103	Other
104	UK
104	Other
105	Other

I want the dataset to look like

ID	UK	France	Spain	Other	Count
101	Y	Y		Y	3
102	Y		Y		2
103				Y	1
104	Y			Y	2
105				Y	1

Re: Transpose Observations to Columns and add Flag

PeterClemmensen — Thu, 27 Mar 2025 11:08:10 GMT

Try this

data have;
input ID Location $;
datalines;
101 UK     
101 France 
101 Other  
102 UK     
102 Spain  
103 Other  
104 UK     
104 Other  
105 Other  
;

proc transpose data = have out = temp(drop = _:);
    by ID;
    id Location;
    var Location;
run;


data want;
   set temp;
   array a _character_;
   count = 0;
   do over a;
      a = ifc(a ne '', 'Y', a);
      if a = 'Y' then count + 1;
   end;
run;

Re: Transpose Observations to Columns and add Flag

anandrc — Thu, 27 Mar 2025 11:16:53 GMT

Thanks for your input.

However, ID column also gets replaced with "Y".

ID	UK	France	Spain	Other	Count
Y	Y	Y		Y	3
Y	Y		Y		2
Y				Y	1
Y	Y			Y	2
Y				Y	1

Re: Transpose Observations to Columns and add Flag

PeterClemmensen — Thu, 27 Mar 2025 11:24:13 GMT

Only if ID is character (it is not in the sample data that I posted).

Otherwise do something like this

data have;
input ID $ Location $;
datalines;
101 UK     
101 France 
101 Other  
102 UK     
102 Spain  
103 Other  
104 UK     
104 Other  
105 Other  
;

proc transpose data = have out = temp(drop = _:);
    by ID;
    id Location;
    var Location;
run;

data want;
   set temp;
   array a UK -- Spain;
   count = 0;
   do over a;
      a = ifc(a ne '', 'Y', a);
      if a = 'Y' then count + 1;
   end;
run;

Re: Transpose Observations to Columns and add Flag

anandrc — Fri, 28 Mar 2025 10:50:43 GMT

Thanks for your suggetion.

Except for ID variable, how do I rename the all the transposed location variables with prefix m_?

For example, in my final dataset i want

m_UK

m_France

m_Spain

m_Other

Re: Transpose Observations to Columns and add Flag

PeterClemmensen — Fri, 28 Mar 2025 10:54:30 GMT

Use the prefix= option in Proc Transpose like this

data have;
input ID $ Location $;
datalines;
101 UK     
101 France 
101 Other  
102 UK     
102 Spain  
103 Other  
104 UK     
104 Other  
105 Other  
;

proc transpose data = have out = temp(drop = _:) prefix = m_;
    by ID;
    id Location;
    var Location;
run;

data want;
   set temp;
   array a m_:;
   count = 0;
   do over a;
      a = ifc(a ne '', 'Y', a);
      if a = 'Y' then count + 1;
   end;
run;