topic Re: Proc sql in SAS Programming

Proc sql

katkarparam — Fri, 08 Jun 2018 11:49:29 GMT

I have one dataset(country) with one column(state),

dataset contain 10000 of record, all are duplicate.

i want to find out distinct record using proc SQL and store all distinct record in separate macro variable.

i.e each distinct record stored in separete macro variable.

Re: Proc sql

Kurt_Bremser — Fri, 08 Jun 2018 12:00:23 GMT

@katkarparam wrote:

I have one dataset(country) with one column(state),

dataset contain 10000 of record, all are duplicate.

i want to find out distinct record using proc SQL and store all distinct record in separate macro variable.

i.e each distinct record stored in separete macro variable.

Don't do that. Mass data belongs in datasets, macro variables are good for certain references etc.

What for would you need that mass of macro variables?

Re: Proc sql

tomrvincent — Mon, 11 Jun 2018 12:46:10 GMT

proc sql noprint;

select distinct state into :state1- from country;

quit;

Re: Proc sql

RW9 — Fri, 08 Jun 2018 12:45:27 GMT

Why? You will hit macro limits, possible restrictions, and have to create a whole lot more code to work with that macro list. Data should go in datasets - clue is in the name. If you need to find data from one table using this distinct list then there are mutliple ways to do it for example:

proc sql;
  create table want as 
  select  *
  from    have 
  where   state in (select distinct state from listds);
quit;

Much simpler, listds dataset cotains the 1000 states, so you can add to that dataset, remove from, manipulate etc. very simple like any dataset, and use it like a list.

Re: Proc sql

katkarparam — Fri, 08 Jun 2018 12:53:23 GMT

I agree with you. But my requirement is i have create new macro variable for each distinct value.

Re: Proc sql

RW9 — Fri, 08 Jun 2018 13:00:48 GMT

So at least 10000 macro variables, I am not sure "requirement" is the right word here.

If you insist:

proc sort data=states out=loop nodupkey;
  by state;
run;

data _null_;
  set loop;
  call symputx(cats('col',put(_n_,best.)),state);
run;

But do be prepared to spend the next 100 years trying to work with that.

Re: Proc sql

katkarparam — Sat, 09 Jun 2018 09:28:43 GMT

for. example purpose I have use the sas default dataset

proc sql noprint;
select distinct origin into :list- from sashelp.cars;
quit;

WARNING: INTO Clause :list through : does not specify a valid sequence of macro variables.

Re: Proc sql

katkarparam — Sat, 09 Jun 2018 09:32:20 GMT

i have create new macro variable for each distinct value.

#This is challenge for me from my colleague
but I am not able to do

Re: Proc sql

Kurt_Bremser — Mon, 11 Jun 2018 06:31:45 GMT

@katkarparam wrote:
i have create new macro variable for each distinct value.

#This is challenge for me from my colleague
but I am not able to do

So you're wasting time on something that two superusers have advised you not to do because it's probably the most stupid way to deal with this kind of issue.

It's not that difficult to create, either. @tomrvincent has already shown you (although his code doesn't work because he missed to give the macro variable name a trailing number) that there is a way to write values into a list of macro variables.

See the documentation of the INTO clause in the SAS 9.4 documentation for the SELECT statement (fourth method of macro variable specification).

Re: Proc sql

katkarparam — Mon, 11 Jun 2018 07:32:24 GMT

thanks @Kurt_Bremser your valuable time.

Re: Proc sql

ChrisBrooks — Mon, 11 Jun 2018 08:19:51 GMT

I agree absolutely with everyone else who says this is not a sensible thing to do. If you were to absolutely require loading a list of that magnitude into memory then a hash table would be the way to go....

Re: Proc sql

katkarparam — Mon, 11 Jun 2018 08:51:36 GMT

sir,
can you give little bit hint
how I can achieve these with Hash Table

thanks in advance..

Re: Proc sql

Kurt_Bremser — Mon, 11 Jun 2018 10:00:37 GMT

The proper way of dealing with issues is not starting with the tools. You start with the issue itself, and then select the tool most suited for the task.

So what for would you use this list of 10000 items?

Re: Proc sql

ChrisBrooks — Mon, 11 Jun 2018 10:15:25 GMT

There’s a nice introduction to the subject here https://support.sas.com/resources/papers/proceedings15/3024-2015.pdf

Re: Proc sql

tomrvincent — Mon, 11 Jun 2018 12:47:23 GMT

Sorry...that should be list1-. I've corrected my original post.

Re: Proc sql

tomrvincent — Mon, 11 Jun 2018 12:48:08 GMT

@Kurt_Bremser thanks for the catch. I've corrected my post to be state1.

Re: Proc sql

tomrvincent — Mon, 11 Jun 2018 12:50:01 GMT

How many distinct state values are there in your dataset?

Re: Proc sql

katkarparam — Tue, 12 Jun 2018 05:26:43 GMT

for this dataset I have 37 distinct values.

Re: Proc sql

katkarparam — Tue, 12 Jun 2018 05:27:14 GMT

thank you.. I will refer it

Re: Proc sql

tomrvincent — Wed, 13 Jun 2018 12:06:27 GMT

Not 10,000. 37.