topic Re: Making categorical values to variables and populating it with it's values in SAS Enterprise Guide

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

ganeshdh — Tue, 21 Nov 2017 03:37:07 GMT

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!

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

Reeza — Tue, 21 Nov 2017 03:51:38 GMT

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!

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

ganeshdh — Tue, 21 Nov 2017 04:05:43 GMT

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!

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

Reeza — Tue, 21 Nov 2017 04:20:40 GMT

@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.

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

Astounding — Tue, 21 Nov 2017 04:52:47 GMT

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.

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

Ksharp — Tue, 21 Nov 2017 12:44:33 GMT

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;