hello
I have this dataset:
data new;
infile datalines;
input ID $ Name $ Nationality $ Price;
datalines;
01 Hans Y 130
02 Pierre Y 100
03 Marko N 80
04 Ivan N 95
05 John Y 120
06 Ana N 75
07 Petra N 45
;
I would like, as result to have new dataset with two new variables which would count the number of Nationality(Y) and Nationality(N), something like this
proc print data=result;
run;
obs Number_of_Y Number_of_N
1 | 3 | 4 |
I got this by using proc sql, created two views, based on them i created new tabel named result. I would like to know how to do this using data step programming or some better way using proc sql queries.
Thank you
Alternative SQL query :
proc sql;
select
sum(nationality="Y") as Number_of_Y,
sum(nationality="N") as Number_of_N
from new;
quit;
PG
Here's a way to do it with PROC SQL, not sure if you want the "obs" field in your result data set or not. Hope this is what you're looking for!
data have;
input ID $ Name $ Nationality $ Price;
datalines;
01 Hans Y 130
02 Pierre Y 100
03 Marko N 80
04 Ivan N 95
05 John Y 120
06 Ana N 75
07 Petra N 45
;
run;
proc sql;
create table want as
select 1 as obs,
sum(case when Nationality="Y" then 1
Else 0 end) as Number_of_Y,
sum(case when Nationality="N" then 1
Else 0 end) as Number_of_N
from have;
quit;
Alternative SQL query :
proc sql;
select
sum(nationality="Y") as Number_of_Y,
sum(nationality="N") as Number_of_N
from new;
quit;
PG
proc freq data=new;
table Nationality;
output out=temp;
run;
proc transpose data=temp(drop=percent) prefix=Number_of_ out=want(drop=_:);
id nationality;
run;
Thanks guys for your help 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.