About Tom

Tom · ‎10-18-2011

If you have it as a macro variable then you can use it as date literal. %let textdate=10JAN2011; %let endmonth = %sysfunc(intnx(month,"&textdate"d,0,E),date9.); %put &textdate &endmonth; 10JAN2011 31JAN2011

Tom · ‎10-18-2011

Almost all procedure will now deliver the output into datasets for you using the ODS output system. http://support.sas.com/documentation/cdl/en/odsug/62755/HTML/default/viewer.htm Also many procedures have options to specify output datasets. In particular for PROC MEANS/SUMMARY there is the OUTPUT statement.

Tom · ‎10-18-2011

Did you try using SAS/Connect?

Tom · ‎10-18-2011

Cannot do it that way. You will need to use a little code generation. You could query the SAS metadata to get a list of the variables and generate the new variable names. proc sql noprint ; select name, trim(name)||'mv' into :vlist separted by ' ' , :vlist2 separated by ' ' from dictionary.columns where libname='WORK' and memname='BASETABLENUM' and type='num' ; quit; data basetabel_imputed; set basetablenum; array wit &vlist; array new &vlist2; do i=1 to dim(wit); new(i) = (wit(i) = .); if new(i) then do; wit(i)= symget(vname(wit(i)); end; end; drop i; run; Now I am confused about why you are using macro variables to transfer the medians. It is much better use SAS datasets to do that. proc sql noprint ; select name , trim(name)||'mv' , trim(name)||'median' into :vlist separted by ' ' , :vlist2 separated by ' ' , :vlist3 separated by ' ' from dictionary.columns where libname='WORK' and memname='BASETABLENUM' and type='num' ; quit; proc summary data=basetablenum ; var &vlist ; output out=basetable_medians median=&vlist3; run; data basetabel_imputed; set basetablenum; if _N_=1 then set basetable_medians; array wit &vlist; array new &vlist2; array median &vlist3 ; do i=1 to dim(wit); new(i) = (wit(i) = .); if new(i) then do; wit(i)= median(i); end; end; drop i &vlist3; run;

Tom · ‎10-14-2011

Sounds like a permission issue. You have read access to the directory so that you can see the file name, but you do not have read access to the actual files. If you want to use the FILEVAR option with an FTP file I found that it does not honor the CD option when using the DIR option. Not sure if there is another way, but you can just use the fully qualified filename in the FILEVAR variable. filename ftpdir ftp cd="&dname" host="&host" user="&user" pass="&pass" ls ; data files ; infile ftpdir truncover; input fname $200. ; run; filename ftp ftp recfm=v host="&host" user="&user" pass="&pass" dir ; data _null_; set files ; where fname like 'x%.sas'; filevar="&dname/"||fname; put 100*'-' / fname / 100*'-'; infile ftp filevar=filevar end=eof ; do while (not eof); input; put _infile_; end; eof=0; run;

Tom · ‎10-14-2011

Perhaps your server requires SFTP instead of normal FTP?

Tom · ‎10-14-2011

FileZilla is probably NOT using mget. It probably retreives the directory and then retrieves the files one by one. SAS does not translate the *, that function is being done by the remote FTP server. Are you sure they do not have embedded spaces or other things so that they do not match the pattern that you are trying to use? Try testing your pattern by using the DIR command instead of the MGET command to see if the files you want are actually there.

Tom · ‎10-14-2011

Did you try checking the ftp server manually from the command prompt? Perhaps your FTP server responds slightly differently than you or SAS expect. From OS command window enter the command: ftp -i hostname Then after giving it your credentials move to the source directory and retreive the files. ftp> cd path ftp> mget 201110*.txt

Tom · ‎10-14-2011

In the first program I found I was putting the wildcards in the FILENAME statement. filename sascode ftp '*.sas' mget recfm=v cd="&xpath" user="&user" pass="&pass" host="&host" ;

Tom · ‎10-14-2011

It is a little hard to figure out the purpose of the different steps. But it seems to me that should move the call to zero out the numeric variables BEFORE the step where you are converting them to character variables. %_CONVERT_MV_TO_ZERO(cur_wk_fig) data cur_wk_fig; length relationship $5; set cur_wk cw_totals; cwhapct=divide(cwhapart,cmeligcount); eligc = put(cmeligcount,comma12.); hapct = put(cwhapct,percent12.1); part= put(cwhapart, comma12.); run; Or maybe even before take the sums.

Tom · ‎10-14-2011

You do not seem to be filtering which files to import. Why not change your FTP to use MGET and change your INFILE to use FILENAME instead of FILEVAR . This way SAS should pull all of the files from the FTP directory and set the FILENAME variable to the name of the file that is currently being read. If you want I could try to dig up some old code where I used this method to download a block of files.

Tom · ‎10-14-2011

Change %let nums=; to %let nums=0; When there are no records selected INTO will not change the value of the target macro variable.

Tom · ‎10-14-2011

I would recommend making the new key variable a different name than the raw variable that you are inputing. Also I am not sure why you want to remove the link to the value of the ADD variable on the extra rows. This code should do what you want. data temp; length key $10 id 8 ; input id1 id2 id3 add $ no$; array ids id1-id3 ; n=0; do i=1 to dim(ids); if ids(i) ne . then do; id = ids(i); n+1; if n > 1 then add=' '; key = catx('_',no,n); output; end; end; drop i n ; cards; 1 2 3 xyz a . 5 6 lkj b 6 7 . mnb c 8 . . klm d . . 10 sdf e run; proc print width=min; run; Obs key id id1 id2 id3 add no 1 a_1 1 1 2 3 xyz a 2 a_2 2 1 2 3 a 3 a_3 3 1 2 3 a 4 b_1 5 . 5 6 lkj b 5 b_2 6 . 5 6 b 6 c_1 6 6 7 . mnb c 7 c_2 7 6 7 . c 8 d_1 8 8 . . klm d 9 e_1 10 . . 10 sdf e

Tom · ‎10-14-2011

The two queries are sorting the output differently. Can you test if that explains the performance difference?

Tom · ‎10-14-2011

For this to work the macro code will need to change to : ... in (&&&list) ...

Online Status	Offline
Date Last Visited	Monday