Desktop productivity for business analysts and programmers

Making categorical values to variables and populating it with it's values

Reply
New Contributor
Posts: 3

Making categorical values to variables and populating it with it's values

Hi all,

 

Can someone please help with this?

 

I want to convert the below table to

 

A1
a3
b1
a5
c21
c4
d3
b2
b5

 

Output:

abcd
11213
324 
55  

 

Thanks Heaps!

Super User
Posts: 22,844

Re: Making categorical values to variables and populating it with it's values

[ Edited ]

Try PROC TRANSPOSE. 

If you have difficulty please post your code and log, with an explanation of what is not working. 

 

EDIT: note that you'll need to make them all the values in the first column the same case, and you can use upcase/lowcase to standardize the values.

 


ganeshdh wrote:

Hi all,

 

Can someone please help with this?

 

I want to convert the below table to

 

A 1
a 3
b 1
a 5
c 21
c 4
d 3
b 2
b 5

 

Output:

a b c d
1 1 21 3
3 2 4  
5 5    

 

Thanks Heaps!


 

New Contributor
Posts: 3

Re: Making categorical values to variables and populating it with it's values

Hi Reeza,

 

I want to remove the duplicates for my output table column names and want to make their corresponding values as columns.

 

proc sql;
create table DSC.Team as
select TEAM,
from DSC.Scorecard
order by Team;
run;

 

proc sort data= DSC.TEAM NODUPKEY;
by TEAM;
run;

 

proc transpose data= DSC.Team
out= DSC.Team_Trans;
ID Team;
run;

 

Here I have made the first row to column names and I am just wondering how to populate the table with its corresponding values

 

Thanks!

Super User
Posts: 22,844

Re: Making categorical values to variables and populating it with it's values


ganeshdh wrote:

Hi Reeza,

 

I want to remove the duplicates for my output table column names and want to make their corresponding values as columns.

 

 


 

Based on your example, you need two steps. 

1. Make all the case the same, i.e. A and a are different values according to SAS. 

2. Use a PROC TRANSPOSE on the data. 

 

The approach you're trying will work but it's more work and includes unnecessary steps which SAS will take care of. In your current PROC TRANSPOSE you don't specify a VAR to include. 

Super User
Posts: 6,537

Re: Making categorical values to variables and populating it with it's values

Because you have multiple observations for each TEAM, as well as spelling differences, you will need multiple steps here.

 

data temp;

set have;

team = upcase(team);

run;

 

proc sort data=temp;

by team;

run;

 

data temp2;

set temp;

by team;

if first.team then group = 1;

else group + 1;

run;

 

proc sort data=temp2;

by group;

run;

 

proc transpose data=temp2 out=want (drop=_name_);

var value;  /* or whatever the variable name is */

id team;

by group;

run;

 

It is conceivable that you would want to add GROUP to the list of variables being dropped.

Super User
Posts: 10,611

Re: Making categorical values to variables and populating it with it's values

use MERGE skill proposed by Me,Arthur.T,Matt

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
input x $ y;
cards;
a	1
a	3
b	1
a	5
c	21
c	4
d	3
b	2
b	5
;
run;
proc sql;
select distinct catt('have(where=(x="',x,'") rename=(y=',x,'))') into : list separated by ' '
 from have;
quit;
data want;
 merge &list;
 drop x;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 131 views
  • 0 likes
  • 4 in conversation