BookmarkSubscribeRSS Feed
katkarparam
Fluorite | Level 6

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.

21 REPLIES 21
Kurt_Bremser
Super User

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

katkarparam
Fluorite | Level 6
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
Kurt_Bremser
Super User

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

katkarparam
Fluorite | Level 6
thanks @Kurt_Bremser your valuable time.
tomrvincent
Rhodochrosite | Level 12
@Kurt_Bremser thanks for the catch. I've corrected my post to be state1.
tomrvincent
Rhodochrosite | Level 12

proc sql noprint;

select distinct state into :state1- from country;

quit;

katkarparam
Fluorite | Level 6
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.
tomrvincent
Rhodochrosite | Level 12
Sorry...that should be list1-. I've corrected my original post.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

katkarparam
Fluorite | Level 6
I agree with you. But my requirement is i have create new macro variable for each distinct value.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

tomrvincent
Rhodochrosite | Level 12
Not 10,000. 37.
ChrisBrooks
Ammonite | Level 13

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

katkarparam
Fluorite | Level 6
sir,
can you give little bit hint
how I can achieve these with Hash Table

thanks in advance..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 1457 views
  • 2 likes
  • 5 in conversation