topic Create Dataset using DISTINCT values only in SAS Data Management

Create Dataset using DISTINCT values only

AZIQ1 — Fri, 10 Jun 2016 16:57:41 GMT

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

Re: Create Dataset using DISTINCT values only

ballardw — Fri, 10 Jun 2016 16:50:54 GMT

Proc sql;

create table want as

select distinct *

from have;

run;

or name the desired variables after the distinct predicate.

Re: Create Dataset using DISTINCT values only

AZIQ1 — Fri, 10 Jun 2016 16:56:10 GMT

Thank you it did not work.

Re: Create Dataset using DISTINCT values only

jklaverstijn — Fri, 10 Jun 2016 16:58:32 GMT

@AZIQ1 wrote:
Thank you it did not work.

What happened? Code and log may help.

- Jan.

Re: Create Dataset using DISTINCT values only

ballardw — Fri, 10 Jun 2016 17:07:15 GMT

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.

Re: Create Dataset using DISTINCT values only

AZIQ1 — Fri, 10 Jun 2016 17:36:01 GMT

data have;

input id var1 var2 var3 var4 var5 var6 var7 var8 var9 ;

datalines;

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

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)

Re: Create Dataset using DISTINCT values only

ballardw — Fri, 10 Jun 2016 17:39:16 GMT

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?

Re: Create Dataset using DISTINCT values only

AZIQ1 — Fri, 10 Jun 2016 18:08:37 GMT

I selected a second var just like you said and it worked.
Thank you