topic Re: Macro with the list of variables initialization in SAS Programming

topic Re: Macro with the list of variables initialization in SAS Programming https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313512#M68133 <P>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...</P><P> </P><P> </P><BR /><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/13166i758EF3D6FD4C645F/image-size/large?v=1.0&px=600" border="0" alt="picture1.png" title="picture1.png" /> Tue, 22 Nov 2016 16:12:56 GMT Uknown_user 2016-11-22T16:12:56Z Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313493#M68124 <P>Hello all,</P><P> </P><P>I am trying to figure out why this code does not return any output:</P><P> </P><P><FONT color="#000080" face="Courier New" size="3"><STRONG>%macro</STRONG></FONT><FONT face="Courier New" size="3"> m(country);</FONT></P><P><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">data work.preselection_&</FONT></FONT><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3">country.</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">;</FONT></FONT></P><P><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">set DM_CMDM.VW_CONSUMER (where= (pk_country_id = </FONT></FONT><FONT color="#800080" face="Courier New" size="3"><FONT color="#800080" face="Courier New" size="3"><FONT color="#800080" face="Courier New" size="3">"&country."</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">) keep=pk_country_id pk_consumer_id lk_classmarket personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING);</FONT></FONT></P><P>run;</P><P><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><STRONG>%mend</STRONG></FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> m;</FONT></FONT></P><P><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><STRONG>%macro</STRONG></FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> <STRONG><I>Demo</I></STRONG>;</FONT></FONT></P><P>PROC SQL noprint;</P><P>select distinct pk_country_id</P><P>into :country1 - :country999</P><P>from DM_CMDM.VW_CONSUMER;</P><P>quit;</P><P><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3">%do</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> I = </FONT></FONT><STRONG><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3">1</FONT></FONT></FONT></STRONG> <FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3">%to</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> &</FONT></FONT><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3"><FONT color="#008080" face="Courier New" size="3">SqlObs.</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">;</FONT></FONT></P><P><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3">%put</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> %m(&&country&I.);</FONT></FONT></P><P><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3"><FONT color="#0000ff" face="Courier New" size="3">%end</FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3">;</FONT></FONT></P><P><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><FONT color="#000080" face="Courier New" size="3"><STRONG>%mend</STRONG></FONT></FONT></FONT><FONT face="Courier New" size="3"><FONT face="Courier New" size="3"> Demo;</FONT></FONT></P><P>%<STRONG><I>Demo</I></STRONG>;</P><P> </P><P> </P><P><FONT face="Courier New" size="3">It probably does exactly what I want it to do but fails to return any output...Here is the message from log:</FONT></P><P> </P><P>data work.preselection_AT; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "AT") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_BG; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "BG") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_CH; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "CH") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_CZ; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "CZ") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_DE; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "DE") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_HK; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "HK") keep=pk_country_id pk_consumer_id lk_classmarket</P><P>personal_marketing PERMANENT_CTRL_GROUP_FLAG GENERAL_MARKETING); run;</P><P>data work.preselection_HU; set DM_CMDM.VW_CONSUMER (where= (pk_country_id = "HU") keep=pk_country_id pk_consumer_id lk_classmarket</P><P> </P><P>It apparently creates the list of countries but no final files are given back at the end. Any suggestions? Thanks</P><P> </P><P>Jiri</P><P> </P> Tue, 22 Nov 2016 15:28:46 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313493#M68124 Uknown_user 2016-11-22T15:28:46Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313504#M68130 <P>You haven't posted any test data so can't see what pk_country_id contains, are you sure the where works?</P> <P>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.</P> <P>You could simplfy your code somewhat:</P> <PRE>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;</PRE> Tue, 22 Nov 2016 15:53:48 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313504#M68130 RW9 2016-11-22T15:53:48Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313506#M68131 <P>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.</P> Tue, 22 Nov 2016 15:57:10 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313506#M68131 Uknown_user 2016-11-22T15:57:10Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313508#M68132 <P>Sorry, miswording.  I don't mean not working, I mean logically it doesn't match.  Say for instance your data looks like:</P> <P>pk_country_id</P> <P>at</P> <P>bm</P> <P> </P> <P>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?</P> <P>Try where upcase(strip(pk_country_id))="AT".</P> Tue, 22 Nov 2016 16:03:53 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313508#M68132 RW9 2016-11-22T16:03:53Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313512#M68133 <P>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...</P><P> </P><P> </P><BR /><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/13166i758EF3D6FD4C645F/image-size/large?v=1.0&px=600" border="0" alt="picture1.png" title="picture1.png" /> Tue, 22 Nov 2016 16:12:56 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313512#M68133 Uknown_user 2016-11-22T16:12:56Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313513#M68134 <P>The word %PUT should not be part of your %DO loop.  The loop should just call the macro:</P> <P> </P> <P>%m (&&country&i.)</P> Tue, 22 Nov 2016 16:11:23 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313513#M68134 Astounding 2016-11-22T16:11:23Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313516#M68135 <P>Ah, <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/4954">@Astounding</a> 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.  </P> Tue, 22 Nov 2016 16:16:56 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313516#M68135 RW9 2016-11-22T16:16:56Z Re: Macro with the list of variables initialization https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313518#M68137 <P>That solved my problem. Thanks very much.</P> Tue, 22 Nov 2016 16:23:58 GMT https://communities.sas.com/t5/SAS-Programming/Macro-with-the-list-of-variables-initialization/m-p/313518#M68137 Uknown_user 2016-11-22T16:23:58Z