proc sql for transpose

[Claudia_SAS]

Hi,

I am looking for some support using Proc SQL in SAS Enterprise Guide.

What I am trying to do is based on the giving table: t1 (Name(string), Var(string)) looking just like in the imagine below

Name	Var
A	Var1
A	Var2
A	Var3
B	Var1
B	Var3
B	Var89
C	Var2
D	Var70

And eventually to get my table looking as:

Name	Var1	Var2	Var3	Var4	….	Var70	…	Var89
A	1	1	1	0	0	0	0
B	1	0	1	0	0	0	0	1
C	0	1	0	0	0	0	0	0
D	0	0	0	0	0	1	0	0

Basically it is a pivot table, where Name A has or has not a value of the Var i...n (1-means had a value, 0-means it doesn't have a value)

Is there an easy way to do in one step using proc SQL?

Thank you for your help!

[PaigeMiller]

Is there an easy way to do in one step using proc SQL?

I don't think there is an easy way to do this, although I guess it depends on your definition of "easy". In fact, I don't think there is a way to do this in PROC SQL.

Nevertheless, it's a snap if you want to use PROC REPORT (but columns with all zeros won't appear).

[Kurt_Bremser]

The task defines the tool. SQL is not good for such issues, but SAS provides tools that make it quite easy:

data have;
input Name $ Var $;
datalines;
A Var1
A Var2
A Var3
B Var1
B Var3
B Var89
C Var2
D Var70
;

data pretrans;
set have;
value = 1;
run;

proc transpose data=pretrans out=trans (drop=_name_);
by name;
id var;
var value;
run;

data want;
retain name var1-var89; /* for variable order */
array vars {*} var1-var89; /* creates empty variables */
set trans;
do i = 1 to dim(vars);
  vars{i} = ifn(vars{i} = .,0,vars{i});
end;
drop i;
run;

[Claudia_SAS]

Thank you! 

what if instead of var 1, var 2,..., (array) i have different values (string only) i.e.: apple, pear, etc

Therefore my giving table is 

Name     Description

A             Apple

A             Pear

A             Red

B             Apple

B             Blueberry

C             Apple

C             Tomato 

And the output table should look:

            Apple          Blueberry     Pear    Red     Tomato

A          1                        0              1          1            0

B          1                        1              0          0            1

C          1                        0              0          0            1

Thank you!             

[PaigeMiller]

---

@Claudia_SAS wrote:

Thank you! 

what if instead of var 1, var 2,..., (array) i have different values (string only) i.e.: apple, pear, etc

Therefore my giving table is 

Name     Description

A             Apple

A             Pear

A             Red

B             Apple

B             Blueberry

C             Apple

C             Tomato 

And the output table should look:

            Apple          Blueberry     Pear    Red     Tomato

A          1                        0              1          1            0

B          1                        1              0          0            1

C          1                        0              0          0            1

Thank you!             

---

proc report data=have;
    columns name description;
    define name/group;
    define description/across;
run;

[PGStats · Accepted solution]

Then I guess you could use:

/* create a view instead of a new dataset */
data temp1 / view=temp1;
set have;
value = 1;
run;

/* transpose the data, this generates missing values instead of zeros */
proc transpose data=temp1 out=want(drop=_name_);
by Name;
id description;
var value;
run;

/* replace the missing values with zeros */
proc stdize data=want out=want missing=0 reponly; 
run;

[Claudia_SAS]

works perfect! thank you!