%LET macro statement from an Excel sheet

[Paige1]

I have an Excel file with NDCs and Categorys. Here is a sample from it:

NDC	Category
00536114941	NSAID
00536308610	NSAID
00536308641	NSAID
00536331301	NSAID
00536331310	NSAID
00603002622	NSAID
57881033632	Opioid
57881033832	Opioid
00093005001	Opioid-other
00093015001	Opioid-other
69344011111	Opioid-NSAID
69344021111	Opioid-NSAID

etc...

I want to create a %LET statement in SAS using the Excel sheet that would do the same thing as:

%LET NSAID = 00536114941 00536308610 00536305641 00536331301 00536331310 00603002622;

%LET Opioid = 57881033632 578810832;

%LET Opioid_other = 00093005001 00093015001;

%LET Opoioid_NSAID = 69344011111 69344021111;

But I want to make it so that if I make changes to the Excel sheet, it will automatically update in SAS--that is I want to use SAS code that creates a %LET statement FROM the Excel file, not copy paste from Excel into SAS.

[Reeza]

I have a feeling if you explain your usage a data driven format will be much more appropriate. You can import the data and create the format from the imported data set so it's a fully dynamic solution.

[andreas_lds]

Storing data in macro-variables is almost always a bad idea and I can't remember any case that wasn't solvable without using macro variables.

Step 1 import the excel file.
Step 2 change the value of Category, so that it contains only valid sas names. Check that you don't modify a value so that it is the same as the value ready in the dataset.
Step 3 use a data step with by and retain to build the value-list per category. On last category use call symputx to create a macro-variable.

If you want code, please provide data in usable from: a data-step using datalines, NO excel-files, please.

[Paige1 · Accepted solution]

Figured it out!

Here it is:

data NDCs;
    length
        ndc       $ 11
        name    $ 15;



    set xl.'CPT'n     (keep=ndc name)
        xl.'ICD_PR'n  (keep=ndc name)
        xl.'ICD_DX'n  (keep=ndc name)
        xl.'DRG'n     (keep=ndc name)
        xl.'NDC'n     (keep=ndc name);
    where
        NDC^= ' ';
run;



libname xl clear;



proc sql;
    create table names as
    select distinct
        name
    from NDCs

;quit;



%macro codes(name);
    %global &name;



    proc sql;
        select distinct
            quote(strip(code)) into: &name separated by ','
        from codes
        where
            name = "&name"
    ;quit;
%mend;



data _null_;
    set names;
    call execute('%codes('||name||');');
run;