Transpose Observations to Columns and add Flag

[anandrc]

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

[PeterClemmensen]

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;

[anandrc]

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

[PeterClemmensen · Accepted solution]

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;

[anandrc]

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

ID

m_UK

m_France

m_Spain

m_Other

[PeterClemmensen]

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;