DATA Step, Macro, Functions and more

Macro with the list of variables initialization

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Macro with the list of variables initialization

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

 


Accepted Solutions
Solution
‎11-22-2016 11:24 AM
Super User
Posts: 5,497

Re: Macro with the list of variables initialization

Posted in reply to Uknown_user

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


All Replies
Super User
Super User
Posts: 7,942

Re: Macro with the list of variables initialization

Posted in reply to Uknown_user

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;
Frequent Contributor
Posts: 84

Re: Macro with the list of variables initialization

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.

Super User
Super User
Posts: 7,942

Re: Macro with the list of variables initialization

Posted in reply to Uknown_user

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".

Frequent Contributor
Posts: 84

Re: Macro with the list of variables initialization

[ Edited ]

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
Super User
Super User
Posts: 7,942

Re: Macro with the list of variables initialization

Posted in reply to Uknown_user

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.  

Solution
‎11-22-2016 11:24 AM
Super User
Posts: 5,497

Re: Macro with the list of variables initialization

Posted in reply to Uknown_user

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

 

%m (&&country&i.)

Frequent Contributor
Posts: 84

Re: Macro with the list of variables initialization

Posted in reply to Astounding

That solved my problem. Thanks very much.

☑ This topic is solved.

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

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