topic proc sql for transpose in SAS Enterprise Guide

proc sql for transpose

Claudia_SAS — Tue, 25 Feb 2020 12:55:04 GMT

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!

Re: proc sql for transpose

PaigeMiller — Tue, 25 Feb 2020 13:06:13 GMT

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

Re: proc sql for transpose

Kurt_Bremser — Tue, 25 Feb 2020 13:23:00 GMT

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;

Re: proc sql for transpose

Claudia_SAS — Tue, 25 Feb 2020 15:00:44 GMT

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!

Re: proc sql for transpose

PaigeMiller — Tue, 25 Feb 2020 15:10:16 GMT

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

Re: proc sql for transpose

PGStats — Tue, 25 Feb 2020 18:35:28 GMT

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;

Re: proc sql for transpose

Claudia_SAS — Tue, 25 Feb 2020 18:48:31 GMT

works perfect! thank you!