Re: Dynamic SQL Case When

david27 · Posted 03-18-2024 05:13 PM

Hello,

I have the below code.

As you see when I invoke the macro it invokes properly with the correct syntax. However When I invoke the macro in the SQL query it throws me error.

%let records_flow_id_value=5;
%let flow_value1=column1;
%let flow_value2=column2;
%let flow_value3=column3;
%let flow_value4=column4;
%let flow_value5=column5;

%macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);

    %do i = 1 %to &nos_iteration.;
        %if &i. = 1 %then %do;
        %put when;
        %end;
        %put sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+;
        %if &i. = &nos_iteration. %then %do;
        %put 0=0 then "-99" when;
        %end;
    %end;
    
    %do j = 1 %to &nos_iteration.;
        %put sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+;
        %if &j. = &nos_iteration. %then %do;
        %put 0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.;
        %end;
    %end;

%mend Assign_99_999;
%Assign_99_999(ColumnName=Column1);

data class;
set sashelp.class;
if age = 12 then column1 ="YES"; else column1="NO";
if age = 13 then column2 ="YES"; else column2="NO";
if age = 14 then column3 ="YES"; else column3="NO";
if age = 15 then column4 ="YES"; else column4="NO";
if age = 16 then column5 ="YES"; else column5="NO";
run;

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

Here is the log:

1                                                          The SAS System                               13:11 Monday, March 18, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable CURMAJ resolves to 9
SYMBOLGEN:  Macro variable FMAJOR resolves to 9
SYMBOLGEN:  Macro variable FMINOR resolves to 4
SYMBOLGEN:  Macro variable FMAINT resolves to 4
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable SYSVLONG resolves to 9.04.01M7P080620
SYMBOLGEN:  Macro variable MAJOR resolves to 9
SYMBOLGEN:  Macro variable CURMAJ resolves to 9
SYMBOLGEN:  Macro variable MINOR resolves to 4
SYMBOLGEN:  Macro variable CURMIN resolves to 04
SYMBOLGEN:  Macro variable MAINT resolves to 4
SYMBOLGEN:  Macro variable CURMNT resolves to 7
SYMBOLGEN:  Macro variable MAINT resolves to 4
MPRINT(HTML5ACCESSIBLEGRAPHSUPPORTED):   ACCESSIBLE_GRAPH
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to 
            "/gpfs/FS1/saswork/SAS_work42220001BBA8_nzxpap1149.nndc.kp.org/SAS_workB78D0001BBA8_nzxpap1149.nndc.kp.org/"
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         %let records_flow_id_value=5;
29         %let flow_value1=column1;
30         %let flow_value2=column2;
31         %let flow_value3=column3;
32         %let flow_value4=column4;
33         %let flow_value5=column5;
34         
35         %macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);
36         
37             %do i = 1 %to &nos_iteration.;
38                 %if &i. = 1 %then %do;
39                 %put when;
40                 %end;
2                                                          The SAS System                               13:11 Monday, March 18, 2024

41                 %put sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+;
42                 %if &i. = &nos_iteration. %then %do;
43                 %put 0=0 then "-99" when;
44                 %end;
45             %end;
46         
47             %do j = 1 %to &nos_iteration.;
48                 %put sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+;
49                 %if &j. = &nos_iteration. %then %do;
50                 %put 0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.;
51                 %end;
52             %end;
53         
54         %mend Assign_99_999;
55         %Assign_99_999(ColumnName=Column1);
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 1
when
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
3                                                          The SAS System                               13:11 Monday, March 18, 2024

0=0 then "-99" when
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to &records_flow_id_value.
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
0>=1 and lengthN(Column1)=0 then "-999" else Column1
56         


57         data class;
58         set sashelp.class;
59         if age = 12 then column1 ="YES"; else column1="NO";
60         if age = 13 then column2 ="YES"; else column2="NO";
61         if age = 14 then column3 ="YES"; else column3="NO";
62         if age = 15 then column4 ="YES"; else column4="NO";
63         if age = 16 then column5 ="YES"; else column5="NO";
64         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
4                                                          The SAS System                               13:11 Monday, March 18, 2024

      system cpu time     0.00 seconds
      memory              729.00k
      OS Memory           20124.00k
      Timestamp           03/18/2024 02:23:51 PM
      Step Count                        21  Switch Count  2
      Page Faults                       0
      Page Reclaims                     52
      Page Swaps                        0
      Voluntary Context Switches        49
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

65         options mprint symbolgen;
66         proc sql;
67         create table _temp_ as
68         select
69             name
70             ,age
71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
SYMBOLGEN:  Macro variable RECORDS_FLOW_ID_VALUE resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 1
when
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable I resolves to 5
5                                                          The SAS System                               13:11 Monday, March 18, 2024

SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
0=0 then "-99" when
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable FLOW_VALUE1 resolves to column1
sum(case when lengthN(column1)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 1
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable FLOW_VALUE2 resolves to column2
sum(case when lengthN(column2)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 2
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable FLOW_VALUE3 resolves to column3
sum(case when lengthN(column3)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 3
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable FLOW_VALUE4 resolves to column4
sum(case when lengthN(column4)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 4
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable FLOW_VALUE5 resolves to column5
sum(case when lengthN(column5)>0 then 1 else 0 end)+
SYMBOLGEN:  Macro variable J resolves to 5
SYMBOLGEN:  Macro variable NOS_ITERATION resolves to 5
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
SYMBOLGEN:  Macro variable COLUMNNAME resolves to Column1
0>=1 and lengthN(Column1)=0 then "-999" else Column1
71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
                                                                                                   __
                                                                                                   22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, |, ||, ~, 
              ~=.  

71             ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
                                                                                                   __
                                                                                                   76
ERROR 76-322: Syntax error, statement will be ignored.

72         from class
73         ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
73       !  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              244.65k
6                                                          The SAS System                               13:11 Monday, March 18, 2024

      OS Memory           20124.00k
      Timestamp           03/18/2024 02:23:51 PM
      Step Count                        22  Switch Count  0
      Page Faults                       0
      Page Reclaims                     11
      Page Swaps                        0
      Voluntary Context Switches        101
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
74         
75         %LET _CLIENTTASKLABEL=;
76         %LET _CLIENTPROCESSFLOWNAME=;
77         %LET _CLIENTPROJECTPATH=;
78         %LET _CLIENTPROJECTPATHHOST=;
79         %LET _CLIENTPROJECTNAME=;
80         %LET _SASPROGRAMFILE=;
81         %LET _SASPROGRAMFILEHOST=;
82         
83         ;*';*";*/;quit;run;
84         ODS _ALL_ CLOSE;
85         
86         
87         QUIT; RUN;
88

PaigeMiller · Posted 03-18-2024 05:21 PM

Please don't tell us you get an error and then not provide the log with the error.

Please show us the entire log. Since this is a macro issue, please turn on the macro debugging options by running this line of code, then run the rest of your code.

options mprint symbolgen;

Copy the log as text and paste it into the window that appears when you click on the </> icon

--
Paige Miller

david27 · Posted 03-18-2024 05:27 PM

Added the log.

PaigeMiller · Posted 03-18-2024 05:42 PM

This is not a macro error, although your use of macros makes it more complicated to see. Your code

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

resolves to

proc sql;
create table _temp_ as 
select 
    name
    ,age
    ,case 0>=1 and lengthN(Column1)=0 then "-999" else Column1 end as Column1
from class
;quit;

and if you had typed this yourself without macros, you would get the same error. This is invalid SQL code. Please look at it carefully and find the SQL error.

--
Paige Miller

Tom · Posted 03-18-2024 05:45 PM

Why do you have statements like:

%put when ;

How is writing WHEN to the SASLOG going to help with generating SQL code??

I cannot even begin to figure out what code it is you are trying to create.

Please explain in words what you are trying to do. Since you want to use a macro to generate some SAS code please show the SQL code you want to generate for the example inputs.

Tom · Posted 03-18-2024 05:47 PM

Are you just trying to replace missing values with -99?

So something like this?

select coalesce(column1,'-99') as column1
from have
;

Mazi · Posted 03-19-2024 02:42 AM

%let records_flow_id_value=5;
%let flow_value1=column1;
%let flow_value2=column2;
%let flow_value3=column3;
%let flow_value4=column4;
%let flow_value5=column5;


options mprint;
%macro Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=);
,case when
    %do i = 1 %to &nos_iteration.;
        sum(case when lengthN(&&flow_value&i.)>0 then 1 else 0 end)+
    %end;
0=0 then "-99"
    when
    %do j = 1 %to &nos_iteration.;
        sum(case when lengthN(&&flow_value&j.)>0 then 1 else 0 end)+
    %end;
0>=1 and lengthN(&ColumnName.)=0 then "-999" else &ColumnName.
%mend Assign_99_999;

data class;
set sashelp.class;
if age = 12 then column1 ="YES"; else column1="NO";
if age = 13 then column2 ="YES"; else column2="NO";
if age = 14 then column3 ="YES"; else column3="NO";
if age = 15 then column4 ="YES"; else column4="NO";
if age = 16 then column5 ="YES"; else column5="NO";
run;
options mprint;

proc sql;
create table _temp_ as 
select 
    name
    ,age
    %Assign_99_999(nos_iteration=&records_flow_id_value., ColumnName=Column1) end as Column1
from class
;quit;

Is this what you are looking for?

SAS Innovate 2025: Call for Content

Classroom Training Available!