Do ...end error

[Ivy]

Dear all,

I want to creata a empty data based on the demographic data .  In the empty data, there are differnet columm based on the group variable. However, somehow,  my " do --end " statements gave me error message. Please see below for the original codes , and log message.

 May you help ? Thank you very much. 

%let indsn = demo ;
%let by = PCM ;

proc sql;
select distinct &by into: by_grp separated by " " from &indsn ;
quit;
%put &by_grp ;

%let gr_cnt=%sysfunc(countw(&by_grp));
%put &gr_cnt;

proc sql;
create table rpt_cat (
var_level char(50),
var char(50),
ptpct char(50),
rord num,
%do j = 1 %to &gr_cnt;
%scan(&by_grp, &j) char(50) ,
%end;
pv char(50)
) ;
quit;

4673 %let indsn = demo ;
4674 %let by = pcm ;
4675
4676
4677 proc sql;
4678 select distinct &by into: by_grp separated by " " from &indsn ;
4679 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

4680 %put &by_grp ;
Non-PCM PCM
4681
4682 %let gr_cnt=%sysfunc(countw(&by_grp));
4683 %put &gr_cnt;
3
4684
4685 proc sql;
4686 create table rpt_cat (
4687 var_level char(50),
4688 var char(50),
4689 ptpct char(50),
4690 rord num,
4691 %do j = 1 %to &gr_cnt;
ERROR: The %DO statement is not valid in open code.
4692 %scan(&by_grp, &j) char(50) ,
WARNING: Apparent symbolic reference J not resolved.
WARNING: Apparent symbolic reference J not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &j
ERROR: Argument 2 to macro function %SCAN is not a number.
4692 %scan(&by_grp, &j) char(50) ,
-
22
ERROR 22-322: Syntax error, expecting one of the following: CHAR, CHARACTER, DEC, DECIMAL,
FLOAT, NUM, NUMERIC, VARCHAR.

4692 %scan(&by_grp, &j) char(50) ,
-
76
ERROR 76-322: Syntax error, statement will be ignored.

4693 %end;
ERROR: The %END statement is not valid in open code.
4694 pv char(50)
4695 ) ;
4696 quit;

[PGStats · Accepted solution]

There are many little problems with that code. Try this instead:

proc sql;
select distinct catx(" ", compress(&by, " -"), "char(50)") into: by_cols separated by "," from &indsn ;
quit;

%put &by_cols ;

proc sql;
create table rpt_cat (
var_level char(50),
var char(50),
ptpct char(50),
rord num,
&by_cols,
pv char(50)
) ;
quit;

(untested)

[Jagadishkatam]

you are trying to execute the %do %end in open code which is not correct. Please try to put the code in a macro and then execute as below

could you please try and let me know

%macro sample;
proc sql;
create table rpt_cat (
var_level char(50),
var char(50),
ptpct char(50),
rord num,
%do j = 1 %to &gr_cnt;
%scan(&by_grp, &j) char(50) ,
%end;
pv char(50)
) ;
quit;
%mend sample;

%sample

[Ivy]

Works pretty ! Thank you so much for pointing out my error.

[RW9]

Hi,

Two tips.  Firstly posting test data in the form of a datastep, and what you want the output to look like, really helps get you code back.  Secondly, what is it your trying to do?  Demography data is normally create with data down the list, or are you creating SDTM DM domain?  Eitehr way this should be part of the programming for the output or dataset.  I can't tell without seeing required output however.

[Ivy]

Thank you very much,  both solutions from PGStats and  Jagadishkatam  work pretty !  🙂  

RW9 , I am trying to summary the demographic information.

Thank you again !

[RW9]

Hi doesn't really tell me anything I am afraid, test data -> example output.  There are numerous ways of getting summarised data which don't need template datasets setup, you could even use generic column names - VARx - for example, save yourself a lot of coding trying to work out what they are called.

[Ivy]

Thank you, RW9 .

If I use Varx, should I edit all the summary from proc freq, proc mean, proc univarite to Var... ,  is that right ?