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
The word %PUT should not be part of your %DO loop. The loop should just call the macro:
%m (&&country&i.)
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;
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.
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".
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...
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.
The word %PUT should not be part of your %DO loop. The loop should just call the macro:
%m (&&country&i.)
That solved my problem. Thanks very much.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.