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; %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;
... View more
