Hi,
I am using this command to get a count of distinct ids, but how can I leverage this command to create a dataset with (all the variables - there are more than 100) based on the distinct ID.
proc sql;
create table WANT as
select id, count( distinct id) as count
from HAVE
where qtr=1
AND TEST=0
group by id
;
quit;
Thanks
The * in SQL is short for all variables. So you get distinct combinations of the remaining variables as well. Some of those variables apparently have more than one value for a given ID.
If you only want distinct values of one variable which value(s) of other variables do you want?
Proc sql;
create table want as
select distinct *
from have;
run;
or name the desired variables after the distinct predicate.
If you have a specific requirement the post the input data and the desired output otherwise there is only guessing.
"Doesn't work" is not very helpful. Did it produce no output?Unexpected output? Generate an error? Post log including the code and any notes or error messages.
data have;
input id var1 var2 var3 var4 var5 var6 var7 var8 var9 ;
datalines;
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1
2 1 1 1 1 1 1 1 1 1
2 1 0 1 0 1 1 0 0 0
2 1 0 1 0 1 1 0 0 0
2 1 0 1 0 1 1 0 0 0
2 1 0 1 0 1 1 0 0 0
3 1 1 1 1 1 1 1 1 1
3 1 1 1 1 1 1 1 1 1
3 1 1 1 1 1 1 1 1 1
3 1 1 1 1 1 1 1 1 1
3 1 1 1 1 1 1 1 1 1
run;
proc sql;
create table want as
select id, count( distinct id) as count
from have
group by id
;
quit;
This gives me 3 distinct count.
proc sql;
create table _1 as
select distinct id, *
from have
group by id
;
quit;
This gives me 4 ( I need 3 as in the first code)
The * in SQL is short for all variables. So you get distinct combinations of the remaining variables as well. Some of those variables apparently have more than one value for a given ID.
If you only want distinct values of one variable which value(s) of other variables do you want?
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.