DATA Step, Macro, Functions and more

Do ...end error

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 78
Accepted Solution

Do ...end error

 

 

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;

 

 

 

 


Accepted Solutions
Solution
‎02-04-2016 09:24 AM
Respected Advisor
Posts: 4,662

Re: Do ...end error

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)

PG

View solution in original post


All Replies
Solution
‎02-04-2016 09:24 AM
Respected Advisor
Posts: 4,662

Re: Do ...end error

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)

PG
Trusted Advisor
Posts: 1,131

Re: Do ...end error

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
Thanks,
Jag
Frequent Contributor
Frequent Contributor
Posts: 78

Re: Do ...end error

Works pretty ! Thank you so much for pointing out my error.
Super User
Super User
Posts: 7,430

Re: Do ...end error

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.

Frequent Contributor
Frequent Contributor
Posts: 78

Re: Do ...end error

Thank you very much,  both solutions from work pretty !  :-)  

 

RW9 , I am trying to summary the demographic information.

 

Thank you again !

 

 

 

 

Super User
Super User
Posts: 7,430

Re: Do ...end error

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.

Frequent Contributor
Frequent Contributor
Posts: 78

Re: Do ...end error

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 ?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 293 views
  • 2 likes
  • 4 in conversation