About ArtC

ArtC · ‎02-17-2012

Macro processing is completed before the DATA step executes. This means that array will not yet exist when the %DO executes. You need to extract the variable name without using the array. The %SCAN macro function will do the trick. Untested code: data notarget_temp1; set &dset.; %do i = 1 %to &nvars.; rename %scan(&varnames,&i,%str( )) = key_&i.; %end; run;

ArtC · ‎02-17-2012

Sorry my bad for not paying attention. When appending letters or numbers to macro variable follow the macro variable name with a dot so that the parser knows where the name ends. USE_&TYPE._CNT_OUTGO=COUNT; Different potential issue. Be sure that &TYPE does not have any leading, trailing, or embedded blanks.

ArtC · ‎02-17-2012

Try changing the DBMS to DMBS=EXCEL rather than EXCELCS.

ArtC · ‎02-17-2012

In this case you do not want to quote the macro variable. Think of it this way. The macro variable resolves to the text that you might have typed. The quotes have nothing to do with this process. You would never name a variable USE_"MMS"_CNT_INCOM=COUNT; the MMS is the resolved value of the macro variable. In your original post. FILE_PERIOD = SUBSTR(&FILE1,7,10) ; fails because &FILE1 does not resolve to a variable name. Instead it is constant text and therefore must be quoted. Remember it is all about how the resolved value of the macro variable is to be used.

ArtC · ‎02-17-2012

If you are populating the Excel table from within SAS the EXCELXP tagset has the capability to take advantage of excel functions. questions on this tagset are best posted in the ODS forum.

ArtC · ‎02-17-2012

There is a DATA step solution for converting numeric to Character in my macro book chapt 6 question 4. Down load the sample code and look for the answer (search the sample code for : ***A1.6.4 ***%ALLCHAR

ArtC · ‎02-17-2012

Add a RUN; statement to each of your CALL EXECUTE steps. The second CALL EXECUTE could be simplified. data _null_; if fileexist("c:\test.xlsx") then call execute('proc import datafile="c:\test.xlsx" out=work.lixi dbms=excelcs replace;run;'); else call execute('data lixi;stop;wingband=.;run;'); run;

ArtC · ‎02-17-2012

Karl is right in that constant text must be quoted, but you my still need to distinguish between incoming data sets. Consider using the INDSNAME= option on the SET statement. Untested code follows: %MACRO STACK(STACKED,FILE1,FILE2) ; DATA B.&STACKED ; SET A.&FILE1 A.&FILE2 indsname=fred; FILE_PERIOD = SUBSTR(fred,7,10) ; RUN; %MEND stacK; %STACK(STACKED_2011DEC31,FILE1_2011DEC31,FILE2_2011DEC31) ; Notice that the variable name FRED is not quoted. I also named the macro on the %MEND - this is just good housekeeping.

ArtC · ‎02-12-2012

Concentrate on the macro language and the use of SQL. The prep guide does a good job of indicating the topics of interest.

ArtC · ‎02-12-2012

If you want the result in a SAS data set then SUMMARY does the trick. If you want a table consider TABULATE or REPORT. data have; input STORE $ YEAR Metric1 Metric2; datalines; Store1 2001 2 5 Store1 2002 3 6 Store1 2003 4 7 Store2 2001 5 1 Store2 2002 6 1 Store2 2003 7 1 Store3 2001 7 3 Store3 2002 9 4 Store3 2003 11 5 run; proc tabulate data=have; class store; var metric1 metric2; table store all, (metric:*mean); run; proc report data=have nowd; column store metric1 metric2; define store /group; define metric:/mean; rbreak after /summarize; run;

ArtC · ‎02-10-2012

For those browsing this thread, the paper cited by Linlin is very good, but it also discusses a number of advanced techniques. If you use this paper and its suggestions be sure that you truly understand the concepts. If you want to get an advanced in depth understanding of the DO loop, this is a very good place to start. But as Data _Null_ said 'be careful'. There are dangers for those who only partially understand the techniques and suggestions.

ArtC · ‎02-09-2012

This will add the variable names. data _null_; set sashelp.class; file "c:\temp\test.txt" dlmstr='^?/'; if _n_=1 then put 'name^?/age^?/sex^?/height^?/weight'; put name age sex height weight; run; There are DATA step solutions that do not require you to know the variable names, but they are more complicated.

ArtC · ‎02-09-2012

Google SAS+Transpose There have been a number of papers done on the TRANSPOSE procedure. Linlin's DATA step array method can ultimately be more flexible, but not necessarily easier for the new user.

ArtC · ‎02-09-2012

The DATA step can be used to write delimited files. Consider this step as a starting point. data _null_; set sashelp.class; file "c:\temp\test.txt" dlmstr='^?/'; put name age sex height weight; run;

ArtC · ‎02-09-2012

Take a look at proc transpose. The following should get you close. data have; input CustomerNo @5 CustomerAccountType $14. Balance; datalines; 001 Saving Account 100.00 001 Term Deposit 200.00 002 Saving Account 400.00 002 Term Deposit 600.00 run; proc transpose data=have out=want; by customerno; id customeraccounttype; var balance; run; proc print data=want; run;

Online Status	Offline
Date Last Visited	‎06-20-2021 07:11 PM