topic Re: Newbie question on loops and macros in SAS Procedures

Newbie question on loops and macros

Samka — Wed, 07 Aug 2013 16:15:30 GMT

cro
The problem: I want to access and pull data from 10 SQL Server tables and aggregate the results. Each time I do it, the first part of the table name will be the same and they will be indexed by the specific 10 trailing ID numbers that are 4 or 5 digits long. The principle is important since I will be doing it several times with different kinds of tables that have different table names but use the same trailing IDs. I would like SAS to grab the number, append it to the table name in DATA step or PROC SQL, pull back the data, and go to the next table. I think I can aggregate the data pretty simply once I have it in 10 tables of the form TABLE_NAME:

1. Is the best technique a %DO loop, or a CALL SYMPUT(N) or should I use something else?

2. How close is the following to what I want to accomplish? Are there obvious errors you can help me debug? Or do I start over with another approach?

NOTE: My WebID variable has to be reformatted from numeric to char; my Startdate variable has to be reformatted from DATETIME23. to mmddyy10.

3. I set up a separate COUNTER field but is there a way to just use the OBS and rename it to Counter to use?

4. When I move to the next step, and have to call TABLE_NAME:||##### what is the correct syntax to concatenate and have SAS know what table to run PROC SQL from? I have a libname dbo. for the tables.

My challenge is that I am doing something very challenging for my skill level, and programming is unforgiving of the least error. Thanks in advance for any assistance.

/*The Command X allows you to send things to the shell.Using it with noxwait means that SAS does not have to wait for the X command before going on.*/

options compress=yes noxwait mlogic mprint;

X "md c:\temp\temp"; **make a new directory if it doesnt exist--this is for temp tables**;

X "md c:\temp\Client"; **make a new directory if it doesnt exist--this is for final tables**;

X "md c:\temp\md"; **make a new directory if it doesnt exist--this is for tables just md**;

libname Client "c:\temp\Client";

libname temptemp "c:\temp\temp"; **assign a libname to the new directory **;

libname mdcont "c:\temp\md";

proc datasets library=temptemp kill;

quit;

proc datasets library=Client kill;

quit;

proc datasets library=mdcont kill;

quit;**clear out the library before generating new tables **;

*********************generic macro declarations****************************;

%macro initiate_libname_connection (libname, provider, server, table, user, password);

libname &libname oledb provider = &provider

properties = ('data source'=&server

/**%if &provider = "Microsoft.Jet.OLEDB.4.5" %then**/

%if &provider = "SQLNCLI10" %then

%do;

);

%end;

%else %do;

'initial catalog' = 'MN_GROK'

%if &user = 'integrated security' %then

%do;

'integrated security'= SSPI);

%end;

%else %do;

'user id' = &user

'password' = &password);

%end;

%mend initiate_libname_connection;

****************end of generic macro declarations ********************************;

**define a libname for my SQL database **;

%initiate_libname_connection (dbo, SQLNCLI10, MN_GROK ,DBAS,'integrated security');

****create table of groupids**********;

data groupid;

input counter groupid1 $ ;

datalines;

1 5725

2 72577

3 73554

4 73927

5 73965

6 74577

7 74238

8 74594

9 7987

10 9954

;

run;

proc contents data=groupid;

run;

%macro mddoit (i, group);

%GLOBAL i group;

%let COUNTER=&I;

%let groupid1= &group ;

%DO %while (&i<11);

DATA md_&group. (Drop=WebID Startdate) ;

RENAME TEMPCON=WEbID;

RENAME TEMPCON1=Startdate;

format Startdate mmddyy10.;

informat Startdate mmddyy10.;

&i=&i +1;

set work.groupid;

TEMPCON=PUT(WEbID, $2.);

TEMPCON1=PUT(datepart(Startdate), mmddyy10.);

format Date1 Date2 Date3 mmddyy10.;

Date1 = today();

Date2 = today() - 365;

Date3 = today() - 547;

format Startdate mmddyy10.;

output;

%END;

%mend mddoit;

%mddoit;

run;

Re: Newbie question on loops and macros

PaigeMiller — Wed, 07 Aug 2013 20:03:53 GMT

This is a rather complicated example involving data tables whose name we don't know and whose content we don't know, and its difficult for us to figure out why it won't work for you. Can you simplify the example, and show us the inputs and desired outputs?

My gut feel is that you will need a %DO loop, but beyond that, I can't really say.

Re: Newbie question on loops and macros

Samka — Wed, 07 Aug 2013 22:52:01 GMT

Thanks, Paige,

Let me try to simplify:

I have a SQL Server Database with a gazillion tables. I am interested in 10 of them of the form:

Dbo.MN_PP_12345, Dbo.MN_PP_2983,
etc.

From those tables I want to pull 8 variables, two of which
must be reformatted (one is a date, the other is a numeric to text), and add 3
date values to compare to the date I just mentioned.

Then, I will aggregate (union) the results of data step or
proc sql.

The total query is very long with similar repetitions of the
same pattern on different kinds of tables and aggregations later on. My feeling is that if I can get the correct
pattern and syntax for this piece, I will be able to replicate for the rest. The 10 numbers will stay the same for
everything so if they are some sort of global variable that I can plug in as
needed, that’s a good thing.

Re: Newbie question on loops and macros

Tom — Wed, 07 Aug 2013 23:30:49 GMT

Sounds like you want to write a macro that will take the base table name as input and then combine and transform all of the various source tables into one output table? If the structure is the same then you can probably do that in one data step by just specifying multiple source datasets in the SET statement.

Something like this might work.

%macro do_one(base);

%local dslist ;

proc sql noprint ;

select cats('dbo.&base._',groupid)

into :dslist separated by ' '

from groupid

;

quit;

data &base ;

set &dslist ;

retain date1-date3 ;

format date1 date2 date3 mmddyy10.;

if _n_=1 then do;

date1 = today();

date2 = today() - 365;

date3 = today() - 547;

end;

tempcon=put(webid, $2.);

tempcon1=put(datepart(startdate), mmddyy10.);

drop webid startdate ;

rename tempcon=webid tempcon1=startdate;

run;

%mend do_one ;

* Set up list of GROUPIDs to use in building source dataset names ;

data groupid;

input groupid $ @@ ;

datalines;

5725 72577 73554 73927 73965 74577 74238 74594 7987 9954

;

* make connection to library with the source datasets ;

libname dbo sqlserver .... ;

* convert the various tables ;

%do_one(md);

%do_one(xxxx);

Re: Newbie question on loops and macros

Patrick — Wed, 07 Aug 2013 23:40:56 GMT

If I understand your requirements correct then you might get away with some code as below.

I've set up an example using SAS Work tables but this should work the same for your DB tables. You simply need to replace 'work' with the libref for you database.

The SAS Access engine then should translate the SAS SET statement to native SQL and send it to the database. Using option "sastrace=',,,d' sastraceloc=saslog nostsuffix" will write to the log if this happens.

/* create test data */
%macro testdata;
%do i=1 %to 10;
    data test_table_&i.;
      var_A=&i;
      var_B='dummy';
    run;
%end;
%mend;
%testdata;

/* this option used to see in the log what the SAS Access engine sends to the database for processing */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;

/* version 1 in case no unwanted table existst with the same naming pattern */
data want;
set work.test_table_: (keep=var_A);
format Date1 Date2 Date3 mmddyy10.;
retain Date1 Date2 Date3;
if _n_=1 then
    do;
      Date1 = today();
      Date2 = intnx('year',today(),-1,'s');
      Date3 = intnx('semiyear',today(),-3,'s');
    end;
run;

/* version 2 in case there are other unwanted tabels with the same naming pattern */
%let Table_List=;
proc sql;
select catx(' ',cats(libname,'.',memname),'(keep=var_A)') into :Table_List separated by ' '
from dictionary.tables
where libname='WORK' and memname like 'TEST_TABLE%'
and scan(memname,-1,'_') in ('1','2','3','4','5','6','7','8','9')
;
quit;

data want2;
set &Table_List ;
format Date1 Date2 Date3 mmddyy10.;
retain Date1 Date2 Date3;
if _n_=1 then
    do;
      Date1 = today();
      Date2 = intnx('year',today(),-1,'s');
      Date3 = intnx('semiyear',today(),-3,'s');
    end;
run;

Re: Newbie question on loops and macros

Samka — Thu, 08 Aug 2013 14:51:40 GMT

Thanks, Tom and Patrick. I have started with Tom's suggestion because it is a bit easier for me to understand--less unfamiliar functions and routines. I am going to push forward with it, and then circle back to Patrick's suggestion as it will take me to new areas of SAS. Thanks, guys--I am really appreciative. Sam