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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: