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!
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;
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).
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;
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!
@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;
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;
works perfect! thank you!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.