I don't see any need to use macros for loading data into DB2. If you want to load data into DB2 directly from SAS you must first ensure you have SAS/ACCESS for DB2 product licensed for your site. Then loading is just a matter of choosing one of the few techniques available. Using the SAS/ACCESS library engine for DB2: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342619.htm for a simple and transparent access to the DBMS (DB2 tables will actually be available "as if" they were SAS tables through the library connected to the DBMS). Or if you care for performance, specifying the BULKLOAD option, which is just the fastest method to put data from SAS to DB2 (replace or resume). Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Just noticed you wanted the previous month, not the current one. As demonstrated previously you just need to use the INTNX function. Just replace: date() with: intnx('MONTH', date(), -1) Sorry for that. More on the INTNX function: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212700.htm ... View more

As always, there is more than one right way do this... For a month/year period extraction I kinda like to use formatted dates and perform an alpha match (which may work also for less/great than comparisons). where put(date(),monyy7.) eq put(LAST_UPDATED,dtmonyy7.) Now, what happens here: date() returns the system date which is converted to text using the date monyy7. format = 'SEP2012' LAST_UPDATE is converted to text using the dtmonyy7. date/time format = 'SEP2012' And a alpha match is performed. Or, you could use the datetime() function instead (and use date/time dtmonyy7 format), which returns the system date an time: where put(datetime(),dtmonyy7.) eq put(LAST_UPDATED,dtmonyy7.) Or, if you're not after an exact match, using the date yymm7. format = '2012M09' where put(date(),yymm7.) gt put(datepart(LAST_UPDATED),yymm7.) Notice the use of the datepart function above to convert LAST_UPDATED as the used format requires strictly a date value (not date/time). More on SAS formats here: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001263753.htm More on PUT function here: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000199354.htm Note: the use of date()/datetime() functions or &SYSDATE9 really depends on performance and how "accurate" you want the date to be. &SYSDATE9 evaluates at the beginning of the session, date()/datetime() at the moment the call is made to this functions. Performance wise, the macro &SYSDATE9 should be "nicer" as it is evaluated once and then is part of the code as a constant value. The function is called each time the where is evaluated. Of course this could be meaningless or meaningful depending on the data's volume. Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Hi Walt. I was referring to my own/Linlin's proposal of code (loading macros through datastep instead SQL select into), no difference at all. As I said, NOB/N is to be used certainly in some %do/%end cycle, and it really doesn't matter there if there is trailing or leading blanks, see: %macro test; %let NOB=   10   ; %do I=1 %to &NOB; %put PREFIX&I; %end; %mend test; %test; Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

From the LRECL option you've specified I'm guessing that maybe this is a fixed rec size file, am I right? If so, there's no CR/LF in the file and you have to specify the option recfm=F, then SAS will import everything to the end of each record (314 in length). Then compress with modifier kpw (keep punction + printable chars) will remove the SUB code (1Ax). data _null_; infile fin lrecl=314 truncover recfm=F; input @; _infile_=compress(_infile_,'','kpw'); put _infile_; run; Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

You're right Linlin, SYMPUTX is quite handy for removing leading/trailing blanks, but for this particular case, I really don't think it's necessary. NOB/N is just a counter that will probably be used on some %do/%end cycle and I think it really won't make a difference having lead/trails blanks there. I'm guessing that only if you happen to load the value/affect into some alpha variable, then SYMPUTX will come handy. If it's numeric it won't make a difference either. Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Which is under the "Windows Clients" Section. Still don't know what are your needs, but if you're interested in interacting with SAS from the Windows Platform you should read this before trying to run the samples: http://support.sas.com/rnd/itech/doc9/dev_guide/dist-obj/winclnt/index.html Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

I would stick with shivas/Tom approach, but for the sake of diversity here's the same thing with datastep: data _null_;      set mydata end=_END;      call symput(cats('alp',_N_),rate); * load value;      if _END then call symput('nob',_N_); * load count; run; Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

I'm sorry but the sample above is not to be executed within SAS. From the looks, this is Basic language from the windows/microsoft .NET framework. What are you trying to achieve? Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Hi. You have to understand that no variable are created inside the datastep at runtime. Datastep is processed in two distinct phases, compile and runtime. It's at compile time that the layout(s) of the destination dataset(s) are defined, not at runtime. The interpreter will scan the code and allocate every variable referenced in the code (on a memory area known as Program Data Vector, or PDV, that will output entirely or partially to the destination dataset(s)), sometimes doing some assumptions about type and length of new variables that aren't what you expected, hence the use of LENGHT statement to guide SAS interpreter to the right assumptions. So when the code runs, everything is pretty well defined in terms of variables, lengths and types. In you're case the SAS interpreter has allocated a new variable (and defaulted to numeric size 😎 before the run time phase because it has seen the reference to it at compiler time. Hope the explanation is clear. Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more