scan in the proc sql

[SASAna]

Hi SAS users,

I need help with the below error (wrong output). I am trying to build the select variables based on the fld values that are being passed in the parameter. I tried alias but still i am unable to get the right values.

fld has 2 values with pipe delimeter and i am looking for SQL to build the below way.

select  distinct ID , NAME from from &input_dataset

CODE is as below - 

%macro util_macro_insert (fld=,Input_Dataset=,Var_Util= , Output_Dataset=);

%let fld_cnt = %sysevalf(%sysfunc(COUNTC(&fld,|))+1);
%put &=fld_cnt;

proc sql;
create table &Output_Dataset. as
select distinct %DO i=1 %to &fld_cnt;
%if &i = 1 %then %do; trim(%scan(&fld,&i,|))   %end;
%if &i > 1 %then %do ; ,trim(%scan(&fld,&i,|)) %end;
%END;
from &input_dataset
;
Quit;

%mend util_macro_insert;

%util_macro_insert (fld=ID | name,Input_Dataset=test1,Var_Util=NBR, Output_dataset =output );

ERROR :  I am getting _TEMA001 and _TEMA002  instead of ID & NAME.

Thanks,

Ana

[PaigeMiller]

When you have problems with macros, place this command at the start of your program and then run the program again.

options mprint symbolgen mlogic;

Then, show us the ENTIRE log for this step, not just the error message. We need to see the code, the NOTEs, the WARNINGs and the ERRORs. Paste the log as text into the box that appears when you click the </> icon. THis makes the log more legible and readable. DO NOT SKIP THIS STEP.

[SASAna]

@PaigeMiller    : I am generating the output but the field names are coming as random SAS variables.

Here is the log

SYMBOLGEN: Macro variable OUTPUT_DATASET resolves to FWAE_UTIL_CLAIMS
SYMBOLGEN: Macro variable FLD_CNT resolves to 2
MLOGIC(UTIL_MACRO_INSERT): %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(UTIL_MACRO_INSERT): %IF condition &i = 1 is TRUE
SYMBOLGEN: Macro variable FLD resolves to ID|NAME
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(UTIL_MACRO_INSERT): %IF condition &i > 1 is FALSE
MLOGIC(UTIL_MACRO_INSERT): %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(UTIL_MACRO_INSERT): %IF condition &i = 1 is FALSE
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(UTIL_MACRO_INSERT): %IF condition &i > 1 is TRUE
SYMBOLGEN: Macro variable FLD resolves to ID|NAME
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(UTIL_MACRO_INSERT): %DO loop index variable I is now 3; loop will not iterate again.
SYMBOLGEN: Macro variable INPUT_DATASET resolves to test1
MPRINT(UTIL_MACRO_INSERT): create table FWAE_UTIL_CLAIMS as select distinct trim(ID) ,trim(NAME) from test1 ;
NOTE: Table WORK.FWAE_UTIL_CLAIMS created, with 97 rows and 2 columns.

[PaigeMiller]

We need to see the ENTIRE log for this section of code, not selected parts.

Paste the log as text into the box that appears when you click the </> icon. DO NOT SKIP THIS STEP.

[ballardw]

Pretty much any time you use a function in SQL you are in effect creating a new variable and if you do not provide a name for the variable the SAS will create one based on the position of the variable creating code.

Consider these two example data sets.

proc sql;
   create table example1 as
   select distinct trim(sex)
   from sashelp.class
   ;
quit;


proc sql;
   create table example2 as
   select distinct trim(sex) as sex
   from sashelp.class
   ;
quit;

The second code shows the fix you need for your example.

[Reeza · Accepted solution]

fld has 2 values with pipe delimeter and i am looking for SQL to build the below way.

I don't know if this is possible with your code, but to avoid this entirely could you pass in a comma delimited string instead? Use %STR() to mask the comma's when you pass the parameter to the macro. Or instead convert the pipes to comma using TRANSLATE? Also, this macro is basically just PROC FREQ, as presented, but I'm assuming you're simplifying things here. 

 

%macro util_macro_insert (fld=,Input_Dataset=,Var_Util= , Output_Dataset=);

proc sql;
create table &Output_Dataset. as
select distinct &fld
from &input_dataset
;
Quit;

%mend util_macro_insert;


%util_macro_insert (fld= %str(ID, name) ,Input_Dataset=test1,Var_Util=NBR, Output_dataset =output );

Or the translate option:

%macro util_macro_insert (fld=,Input_Dataset=,Var_Util= , Output_Dataset=);

%let fld = %sysfunc(translate(&fld, ',', '|'));
%put &fld.;

proc sql;
create table &Output_Dataset. as
select distinct &fld
from &input_dataset
;
Quit;

%mend util_macro_insert;


%util_macro_insert (fld= age|sex ,Input_Dataset=sashelp.class,Var_Util=NBR, Output_dataset  =want );

EDIT: Adding in the PROC FREQ version because...

%macro util_macro_insert (fld=,Input_Dataset=,Var_Util= , Output_Dataset=);

%let fld = %sysfunc(translate(&fld, '*', '|'));

proc freq data=&input_dataset NOPRINT;
table &fld / out = &output_dataset (drop = count percent);
run;

%mend util_macro_insert;

[SASAna]

Thank you so much. I was coding it in complex way .Answer is such a easy way 🙂

Thanks.
Ana