how to create a macro using dataset values

[HitmonTran]

Hi,

I am trying to create a macro that hold all avisit values, that way i don't have to manual check the data if there's new visits in the future.

Here is my starting point, getting all avisitn values in adlb then using avisitn values to create a table shell/dummy dataset "&avisitn".

proc sort data=adlb out=shell_ (keep=avisitn) nodupkey;
by avisitn;
run;


data shell;
   set shell_;
	  do ord1= &avisitn;  /*avisitn values go here*/
	  output;
	  end;
run;

[smantha · Accepted solution]

Your question is not very clear. If you want a list of all unique values as a single macro variable you can use

proc sql;
select distinct avisitn into: avisitn separated by ',' from adlb;
quit;
%put &avisitn;

if you are doing the way you want

data _null_;
length avisitn_string $32767.;
set shell_ end=last;
retain avisitn_string '';
 avisitn_string = strip( avisitn_string)||','||strip(put(avisitn,$5.));
if last then call symputx('avisitn',avisitn_string);
run;
%put &avisitn.;

[ChrisNZ]

>create a table shell/dummy dataset "&avisitn".

Do you want to create one data set by visit?

I can't think of a reason where this could ever be justified.

What's the purpose?

Anyway, if that variable is numeric, this does what you seem to be asking:

proc sql noprint;
  select distinct AVISITN into: avisitn separated by ',' from ADLB order by AVISITN;
quit;
%put &=avisitn;

data X;
  do ORD1= &avisitn.;  /*avisitn values go here*/
	  /* Some code */
  end;
run;