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

I can't seem to find out a way to carryout the process captioned in subject. A similar smaller piece of code I am trying to use is somewhat like the following:

DATA _NULL_;

%LET Entries = 6;

%LET Array Yrs {&Entries} _temporary_ (2007 2008 2009 2010 2011 2012);*List Years;

%LET Array Mns {&Entries} _temporary_ (3 3 3 3 3 3);*List Months;

PROC SQL;*Create The Initial Table;

  CREATE TABLE FAQ AS SELECT * FROM FAQ_NF.DATA_2006_03; *Data of March 2006;

QUIT;

%MACRO First();

  %DO A=1 %TO &Entries.;*Data of only requisite months of years;

    %Second(&Mns{&A.},&Yrs{&A.});

  %END;

%MEND First;

%MACRO Second(MTH, YR);

  PROC SQL;

    INSERT INTO FAQ

  SELECT * FROM FAQ_NF.DATA_&YR._%sysfunc(putn(&MTH,z2.));*Refer Data Tables Which Have Names Like 2007_01 ... 2012_09 and are placed in the library FAQ_NF;

  Quit;

%MEND Second;

%First; *Start The process by calling the first macro;

ODS CSV FILE = "&OUTP.CSVFILE.csv";

PROC PRINT data=FAQ_RESULTS;

run; quit;

ods _all_ close;

Here the LOG File represents that instead of using each element separately it is using the array's value 2007 2008 2009 2010 2011 2012 in a single reference.

Please see if any of you experts can help n advise in this regard.

Thanx in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
FarazA_Qureshi
Calcite | Level 5

Wow Pat!

Sure did find the Tight Looping article and codes at http://www.sascommunity.org/wiki/Tight_Looping_with_Macro_Arrays to be a treasure of integrated code.

Sure was helpful to succeed in a simple sample code like:

%ARRAY(YRSnMNS, VALUES = 2010_01 2011_02 2012_03);

PROC SQL;

  CREATE TABLE xFAQ AS SELECT * FROM FAQ_TEST.DATA_2010_01 WHERE CUSTOMER = 'XYZ';

QUIT;

%MACRO DOIT(XXX);

  PROC SQL;

  INSERT INTO xFAQ SELECT * FROM FAQ_TEST.DATA_&XXX;

  QUIT;

%MEND;

%DO_OVER(YRSnMNS, MACRO=DOIT);

A matchless treasure for sure. Thanks for your advice and suggestion anyway but do note that one can't compare UNION ALL with INSERT INTO. Both remain to be separate and totally different and usage of Macros does reduce your time after all.

Sure do oblige your interests and disclosing the fact of Arrays not freely available for Macros and usage of Listing Approach as per http://caloxy.com/papers/58-028-30.pdf but can't deny the importance of Macro Arrays that make a repetitive job more easy, a treasure by Ted Clay.

Thanx again buddy!

View solution in original post

9 REPLIES 9
Reeza
Super User

That looks like a bit of a mess that I can't follow, so either its well beyond my skills or missing stuff. You do say its a smaller piece of code, is the above meant to run on its own or dependent on other code as well.

The whole thing looks like a way to append certain datasets into a master table, does that sound correct?

I don't think there is such a thing as a macro array either automatically defined anyways. 

FarazA_Qureshi
Calcite | Level 5

You r quite right in guessing that the major objective is to append data of specific months of specific years.

The first macro uses the global macro variables of years, month, and number of datasets to b considered in this regard which are also named as Data_Year_Month

The first macro is to pass the array variables of years and months to the second macro.

Can't an array variable be used in a macro?

I want the second macro to run only on 3rd month of the years declare

If u find it as a mess what would b your recommended approach?

Patrick
Opal | Level 21

You are taking about arrays but the only place where "array" appears in your code is as part of an syntactically invalid %let statement. You should try and post as much valid code as you can. What you've posted here looks to me more like "pasted together" code snippets from somewhere else without having tried to execute them.

There are various issues in the code you've posted.

1. No, you can't use array processing in macro code. Array is data step.

2. The SQL operator for concatenating tables is UNION: create table xy as select * from a outer union corr select * from b outer union corr ......

3. SAS macro language is very powerful but I would call it a best practice to only use it if required and you can't solve a problem without it. Macro code is harder to read and debug than SAS data step / Proc code so it's to be used with care.

Below a code example concatenating a selected range of tables. The code might not 100% do what you're after but I hope it will give you a good head start.

/* create sample source data */
libname FAQ_NF (work);

data FAQ_NF.data_2006_10 FAQ_NF.data_2006_11 FAQ_NF.data_2006_12 FAQ_NF.data_2007_01 FAQ_NF.data_2007_02;
  set sashelp.class;
run;

/* define start and stop year and month */
%let Start=2006_11;
%let Stop=2007_01;

/* define where condition for subsetting data */
%let condition=where name ne 'Alfred';

/* create macro var &Table_List with list of all table names in range */
proc sql noprint;
  select memname into :Table_List separated by ' '
  from dictionary.tables
  where libname='FAQ_NF' and memname between "DATA_&Start" and "DATA_&Stop"
  ;
quit;

/* create target data set */
data want;
  set &Table_List indsname=_indsnm;
  &condition;
  length source_ds $ 41.;
  source_ds=_indsnm;
run;

FarazA_Qureshi
Calcite | Level 5

Thanx for disclosing the fact that ARRAY can't be used in macros.

By the way code has been used before being provided and works perfectly with continuous loops. Furthermore UNION is not the only mode and INSERT INTO has been used because of reasons of change in data structures and selection criteria more effectively.

Do note that macro is necessary when data is spread over a period of 10 years.

It definitely cant be  referring each of the 120 month separately

Can't an array variable's value even be passed to a macro to carry out the requisite steps?

SAS obviously can't be lacking such a necessary feature.

Reeza
Super User

Why is a macro "necessary" when data is spread over 10 years? 

An array in SAS is not the same as in other languages, but there are other, might I mention easier, ways to accomplish what you want. 

Perhaps try to consider what SAS can do and do efficiently, not how to do something the same as another language in SAS.

FarazA_Qureshi
Calcite | Level 5

Thanx for your interest Reeza! However, as I mentioned the actual code is working perfectly in terms of efficiency even though not easily, because of macros carrying out the same, in case of non-breaking continuous loops. Arrays are needed only in some specific conditions, where non-contiguous values are to be passed on, but as conveyed by Patrick it looks like SAS can't have its macros be called with values of only array variable(s) being passed on.

Thanx anyway.

Patrick
Opal | Level 21

There are always multiple ways of how to achieve something using SAS. If you say "code has been used before being provided and works perfectly with continuous loops" then can you please post this code. Because what you've posted will not work.

For example running your %let statements and this is what happens:

23         %LET Entries = 6;

24         %LET Array Yrs {&Entries} _temporary_ (2007 2008 2009 2010 2011 2012);*List Years;

NOTE: A missing equal sign has been inserted after the variable name ARRAY.

25         %LET Array Mns {&Entries} _temporary_ (3 3 3 3 3 3);*List Months;

NOTE: A missing equal sign has been inserted after the variable name ARRAY.

26        

27         %put Arrays: &array;

Arrays: Mns {6} _temporary_ (3 3 3 3 3 3)

There won't be an macro variable "Array Yrs" (a blank is not a valid part for naming variable in SAS). I was actually amused to see that SAS inserts the missing equal sign and doesn't just throw an error.

So there gets a macro variable "&array" created - and the 3rd %let statement will overwrite the assigned value of the 2nd %let statement. But then this macro variable "&array" is nowhere used in the subsequent code. And this is working code??

"Furthermore UNION is not the only mode and INSERT INTO has been used because of reasons of change in data structures..."

???? And why should it then work with a INSERT INTO but not with a UNION ???? I know that you can do it the way it's done in the code you've posted - but it's just not efficient. There is a reason why the UNION set operator has been introduced as part of the SQL language.


"The first macro is to pass the array variables of years and months to the second macro. Can't an array variable be used in a macro?"

You can pass in a string of "words" and you then loop over this string using the %scan() function.

But again: There is too much macro in your code. The thing is: Table names are available in "dictionary.tables" (or also in "sashelp.vtable"). So the only thing you need to do is to set-up a query which returns the table names you want based on some input parameters for selection. Then you can store the retrieved table names in a SAS macro variable (as "words" in string). That's what below code does (as already posted before):

/* create macro var &Table_List with list of all table names in range */

proc sql noprint;

  select memname into :Table_List separated by ' '

  from dictionary.tables

  where libname='FAQ_NF' and memname between "DATA_&Start" and "DATA_&Stop"

  ;

quit;

I suggest that you take a step back and re-think the approach you've taken. And if you can post us exactly what you have and what you need (eg. the possibility to select only certain months per year) then I'm sure someone here is very able to come up with a neat solution. Of course it's always highly appreciated if people see that you've already invested some time in development and debugging.

FarazA_Qureshi
Calcite | Level 5

Wow Pat!

Sure did find the Tight Looping article and codes at http://www.sascommunity.org/wiki/Tight_Looping_with_Macro_Arrays to be a treasure of integrated code.

Sure was helpful to succeed in a simple sample code like:

%ARRAY(YRSnMNS, VALUES = 2010_01 2011_02 2012_03);

PROC SQL;

  CREATE TABLE xFAQ AS SELECT * FROM FAQ_TEST.DATA_2010_01 WHERE CUSTOMER = 'XYZ';

QUIT;

%MACRO DOIT(XXX);

  PROC SQL;

  INSERT INTO xFAQ SELECT * FROM FAQ_TEST.DATA_&XXX;

  QUIT;

%MEND;

%DO_OVER(YRSnMNS, MACRO=DOIT);

A matchless treasure for sure. Thanks for your advice and suggestion anyway but do note that one can't compare UNION ALL with INSERT INTO. Both remain to be separate and totally different and usage of Macros does reduce your time after all.

Sure do oblige your interests and disclosing the fact of Arrays not freely available for Macros and usage of Listing Approach as per http://caloxy.com/papers/58-028-30.pdf but can't deny the importance of Macro Arrays that make a repetitive job more easy, a treasure by Ted Clay.

Thanx again buddy!

Patrick
Opal | Level 21

Hi Faraz

We definitely had some communication issues here...

Happy for you that you found some neat SAS macros doing the job for you.

I still believe that using a SQL Insert for the purpose of appending tables is sub-optimal. In the code you've posted now I would suggest that you use a Proc Append instead.

Thanks

Patrick

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
  • 9 replies
  • 7076 views
  • 8 likes
  • 3 in conversation