DATA Step, Macro, Functions and more

Proc sql

Reply
Contributor
Posts: 20

Proc sql

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.

Super User
Posts: 10,280

Re: Proc sql

Posted in reply to katkarparam

@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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 20

Re: Proc sql

Posted in reply to KurtBremser
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
Super User
Posts: 10,280

Re: Proc sql

Posted in reply to katkarparam

@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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 20

Re: Proc sql

Posted in reply to KurtBremser
thanks @KurtBremser your valuable time.
Regular Contributor
Posts: 217

Re: Proc sql

Posted in reply to KurtBremser
@KurtBremser thanks for the catch. I've corrected my post to be state1.
Regular Contributor
Posts: 217

Re: Proc sql

[ Edited ]
Posted in reply to katkarparam

proc sql noprint;

select distinct state into :state1- from country;

quit;

Contributor
Posts: 20

Re: Proc sql

Posted in reply to tomrvincent
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.
Regular Contributor
Posts: 217

Re: Proc sql

Posted in reply to katkarparam
Sorry...that should be list1-. I've corrected my original post.
Super User
Super User
Posts: 9,599

Re: Proc sql

Posted in reply to katkarparam

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.

Contributor
Posts: 20

Re: Proc sql

I agree with you. But my requirement is i have create new macro variable for each distinct value.
Super User
Super User
Posts: 9,599

Re: Proc sql

Posted in reply to katkarparam

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.

Regular Contributor
Posts: 217

Re: Proc sql

Not 10,000. 37.
Valued Guide
Posts: 595

Re: Proc sql

Posted in reply to katkarparam

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....

Contributor
Posts: 20

Re: Proc sql

Posted in reply to ChrisBrooks
sir,
can you give little bit hint
how I can achieve these with Hash Table

thanks in advance..
Ask a Question
Discussion stats
  • 21 replies
  • 224 views
  • 2 likes
  • 5 in conversation