Proc sql

[katkarparam]

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.

[Kurt_Bremser]

---

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

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]

---

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

thanks @Kurt_Bremser your valuable time.

[tomrvincent]

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

[tomrvincent]

proc sql noprint;

select distinct state into :state1- from country;

quit;

[katkarparam]

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]

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

[RW9]

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]

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

[RW9]

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]

Not 10,000. 37.

[ChrisBrooks]

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]

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

thanks in advance..