%let mth=%substr(%sysfunc(year(&C_ME),4.),3,2)%sysfunc(month(&C_ME),Z2.);   or, more simply, as     %let mth=%sysfunc(putn(&C_ME.,yymmn4.));   as Reeza said           @Reeza, this behavior of CATS with sysfunc is another one of those bug/feature scenarios, if you ask me.  The CATS function 'conviently' interprets the datatype as being numeric and as a result trims the leading zero. ... View more

Assigning the value to a macro variable causes the intnx (or whatever method one uses) to be executed only once. Putting it into the later data step causes it to be executed with every iteration. The old-hand programmer in me cringes at that. ... View more

Two ways come to mind for how to do this. One is to use an array of the asset variables, then use the value in the month variables to index into the array. Another way to do this is to construct a variable name as a string, then call the VVALUEX function to get the value of that variable. The array solution is probably the most efficient, yet you may have be crafty with defining the upper and lower bounds of the array. Both of these methods are shown at the bottom of this post. A link to the doc for VVALUEX is here: http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#/documentation/cdl/en/lrdict/63026/HTML/default/a002233818.htm [pre] /* Create sample dataset */ data info; input ID (assets0908 assets0909 assets0910 assets0911) (:comma.) (month1 month2 month3) ($); datalines; 1 $109 $200 $150 $190 0909 0910 0911 2 $40 $20 $10 $5 0908 0909 0910 ; /* Use month to index into ASSETS array */ data sums(keep=id asset_sum); set info; array assets[0908:0911] assets:; array month month:; asset_sum = 0; do j = 1 to dim(month); month_num = input(month , BEST12.); asset_sum + assets[month_num]; end; run; /* Sum variables that are specified in the MONTH1-MONTH3 variables * (1) Construct variable name and place in ASSET_VARNAME * (2) Call VVALUEX and pass ASSET_VARNAME to get value of asset variable * (3) Convert character value returned by VVALUEX into a numeric value. * (4) Accumulate numeric value into ASSET_SUM */ data sums2(keep=id asset_sum); set info; array month month:; asset_sum = 0; do j = 1 to dim(month); asset_varname = 'assets' || left(month ); asset_value = vvaluex(asset_varname); asset_sum + input(asset_value, best32.); end; run; proc print data=sums; run; [/pre] Output: [pre] Obs ID sum 1 1 540 2 2 70 [/pre] ... View more

Thank you Cynthia and Peter! It worked!! ... View more

Thank you Scott! This is very helpful. ... View more

Would this help? [pre] proc summary data=MYDATA nway; class ID; var FIRM_:; output out=TOTAL_SALES(drop=_:) sum=; run; data NB_FIRMS(drop=J); set TOTAL_SALES; array X FIRM_:; do J=1 to dim(X); NB_FIRMS=sum(NB_FIRMS, X > 0); end; *rather than the loop above, use this if you have missing values when there is no sales; NB_FIRMS2=n(of X ); run; [/pre] proc sql cannot use the colon in this manner unfortunately, and if you must use sql you'll have to create a macro variable containing the list of variables as the previous post shows. Message was edited by: Chris@NewZealand Simplified the reply. ... View more

Thank you Cynthia!! It worked! And thank you for explaining the differences between the order and group usage options. ... View more

Just a tad curious what you will do when crossing a year boundary, where one string is not in the same year as the other? Suggest you still take in the two macro variables but drive the %DO / %END process using SAS DATE values represented as macro variables, and then truncate the output format of your low "yymm" and high "yymm" data strings as needed. This would mean using %SYSFUNC and PUTN / INPUTN to convert the yymm to yymm01, using INPUTN and then use PUTN, if needed, to generate a truncated DATE variable string. You will need to consider using the INTNX function for your processing, possibly. Some examples: %LET YYMM = 0905; %LET MYDATE = %SYSFUNC(INPUTN(&YYMM.01,YYMMDD.)); %LET MYDDMMYYYY = %SYSFUNC(PUTN(&MYDATE,DATE7.)); %LET MYYYMM = %SYSFUNC(PUTN(&MYDATE,YYMMN4.)); %PUT _USER_; Scott Barry SBBWorks, Inc. ... View more

Hi: 1) yes, using tagsets.excelxp (using the NEWFILE= option) 2) yes, using tagsets.excelxp (the code below creates one sheet for every name in sashelp.class) 3) I do not believe this is possible with SAS 4) tagsets.excelxp has some printing/print area instructions 5) tagsets.excelxp has some printing/ print area instructions Look at the doc=help output in the SAS Log for 4 & 5 info. You may still need an Excel macro depending on the formatting you want/need to do. cynthia [pre] proc sort data=sashelp.class out=class; by name; run; ods tagsets.excelxp file='testout.xml' options(doc='Help'); proc print data=class; by name; run; ods tagsets.excelxp close; [/pre] ... View more

Cynthia, Thank you for all your recommendations - they were excellent and I am a much better programmer for that!! ... View more

Peter, The INTNX() worked beautifully! Thank you for your suggestion . AJ ... View more