BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Uknown_user
Quartz | Level 8

Hello all,

 

I am trying to figure out why this code does not return any output:

 

%macro m(country);

data work.preselection_&country.;

set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "&country.") keep=pk_country_id pk_consumer_id lk_classmarket personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING);

run;

%mend m;

%macro Demo;

PROC SQL noprint;

select distinct pk_country_id

into :country1 - :country999

from DM_CMDM.VW_CONSUMER;

quit;

%do I = 1 %to &SqlObs.;

%put %m(&&country&I.);

%end;

%mend Demo;

%Demo;

 

 

It probably does exactly what I want it to do but fails to return any output...Here is the message from log:

 

data work.preselection_AT; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "AT") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_BG; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "BG") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_CH; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "CH") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_CZ; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "CZ") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_DE; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "DE") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_HK; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "HK") keep=pk_country_id pk_consumer_id lk_classmarket

personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;

data work.preselection_HU; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "HU") keep=pk_country_id pk_consumer_id lk_classmarket

 

It apparently creates the list of countries but no final files are given back at the end. Any suggestions? Thanks

 

Jiri

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

The word %PUT should not be part of your %DO loop.  The loop should just call the macro:

 

%m (&&country&i.)

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You haven't posted any test data so can't see what pk_country_id contains, are you sure the where works?

Secondly, its rarely a good idea to split your data up, just looking at the code to split it, you now need to replicate all that each time to use the data.  By processing is both quicker, easy to program and simpler.

You could simplfy your code somewhat:

proc sort data=dm_cmdm.vw_consumer out=loop nodupkey;
  by pk_country_id;
run;
data _null_;
  set loop;
  call execute(cats('data work.preselection_',pk_country_id,'; set dm_cmdm.vw_consumer (where=(pk_country_id="',pk_country_id,'";run;'));
run;
Uknown_user
Quartz | Level 8

Thanks for your reply. If the where statement were not working, either the error would be returned or the empty set of tables would still be produced.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, miswording.  I don't mean not working, I mean logically it doesn't match.  Say for instance your data looks like:

pk_country_id

at

bm

 

Then the where "AT"="at" will not work, as the case is different.  As I can't see your data I can't tell.  Maybe there is a space before or after, maybe it is a formatted variable?

Try where upcase(strip(pk_country_id))="AT".

Uknown_user
Quartz | Level 8

It is not problem with the case sensitive variables or anything like that. I believe, if not matched, it would still produce an empty table. The problem I tried to describe is that there is no Output tab (and no produced tables in the destination, after running the code, only Log tab appears so I end up with Program tab and Log tab...

 

 


picture1.png
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Ah, @Astounding has it.  The %put just puts the resolved text of the macro out to the log, it doesn't execute the code.  Its a bit like putting plain text out to your log.  

Astounding
PROC Star

The word %PUT should not be part of your %DO loop.  The loop should just call the macro:

 

%m (&&country&i.)

Uknown_user
Quartz | Level 8

That solved my problem. Thanks very much.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1189 views
  • 2 likes
  • 3 in conversation