About SASJedi

SASJedi · ‎12-18-2011

Could you post a sample of the code that's giving you problems? This code writes SAS macro code to an external text file, and doesn't resolve until the later %INCLUDE. data _null_; file 'test.txt'; put '%PUT NOTE: The date is %sysfunc(today(),mmddyy10.);'; run; /* Open and read the text file - the macro code is not yet resolved... */ %include 'test.txt';

SASJedi · ‎11-23-2011

You can't use the LIKE operator in this context (this operator is only for use in WHERE statements) - but you CAN use a PERL regular expression and the PRXMATCH function to accomplish the same thing: data l2; set l; if prxmatch('/na_\w*/',id) then p=1; run;

SASJedi · ‎11-01-2011

Luanne, If you have control of the Oracle export process, check out these instructions from the Oracle web site on how to get the output in standard CSV format (with quotes around the header values that are giving you so much grief) http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059 This process would be easier if you have SAS/Access to Oracle or SAS/Access to ODBC licensed and installed on your PC. Then you could just attach to Oracle with a LIBNAME statement and use the Oracle tables just like SAS data sets. You can check to see what you have licensed by submitting this code and reviewing the results in your log: PROC SETININT NOALIAS; RUN; If you do have SAS/Access to Oracle licensed, but it's not working for you check out http://support.sas.com/techsup/technote/ts703.pdf for instructions on how to verify the installation and for sample code to access your Oracle tables with a LIBNAME statement.

SASJedi · ‎11-01-2011

sks, There is a possible problem with using CONTAINS. For example, you say you only want rows that start with REJECTED in the STATUS variable text. If STATUS includes the phrase "was not rejected", the CONTAINS operator would select that observation. There are simpler ways of concatentating a % sign onto your macro variable value, but I'm assuming you chose the data step for your own good reasons. So give this code sample a whirl: /* Make a data set to play with */ data de_services_statistics; infile datalines dlm='*'; input ID Status $35.; datalines; 1*Don't choose me. 2*REJECTED - pick me! 3*I'm not rejected. ; run; /* Test the code */ %let de_status=REJECTED; data _NULL_; /* SYMGET retrieves macro variable values at execution time */ /* The CAT series of functions make text manipulation easier than with the */ /* concatenation operators (|| or !!) */ whereclausesummary = CAT("upcase(status) LIKE '",cats(symget('de_status'),"%'")); /* SYMPUTX is a newer version of SYMPUT with extra features */ call symputx("whereclausesummary",whereclausesummary); run; proc sql; select * from de_services_statistics where &whereclausesummary ; quit;

SASJedi · ‎10-20-2011

If the ODS solution is not feasible, check to see if you have licensed SAS/Access to PC File Formats for your Unix box, and if so, verify that the the SAS administrator has installed and configured the PC File server that comes with the SAS/Access product on a Windows box on your network. With this infrastructure in place, you can read and write to Excel spreadsheets from your Unix SAS session using a LIBNAME statement. The LIBNAME statement would look something like this: libname xlr PCFILES path="D:\folder\MyFile.xls" server=<server name> port=<port#>; You would replace <serve name> with the name of your server (somethin like pcff.mydomain.com) and <port#> with the port number used to access the PC File server (the default value is 8621). If you want to write data to specific cell ranges in the Excel spreadsheet, it is best to set up NAMED RANGES in the spreadsheet first. When you assign a libname to an Excel workbook that contains named ranges, each named range appears as a separate data set in the SAS library. Due to limitations of the MS Jet Engine, which SAS Access uses to read and write to Excel, you can't overwrite data in the existing cells - you first have to empty the range then write in the new data. For example, say the workbook MyFile.xls contains a named range called "PutDataHere" and you want to fill it with the data from SASHELP.CLASS. The following code should do the trick: libname xlr PCFILES path="D:\folder\MyFile.xls" server=pcff.mydomain.com port=8621; proc sql; drop table xls.PutDataHere; quit; data xls.PutDataHere; set sashelp.class; run;

SASJedi · ‎10-20-2011

Yep, my bad - CARDS is unbuffered, and the END= option won't work here...

SASJedi · ‎10-18-2011

The problem is that when you call the macro, the commas in the macro variable make it look as if there are too many parameters for the macro: %let list1 = 101,102,103,104,105; %test(&list1); This will resolve to: %test(101,102,103,104,105); and that looks like a macro call with several parameters, not just one. You will need to mask the commas using an execution-time macro quoting function like SUPERQ. If you choose SUPERQ, your code would look like this: %let list1 = 101,102,103,104,105; %let list2 = 106,107,108,109,110; %macro test(list); proc sql; connect to mysql(server); create table table1 as select * from connection to mysql ( select * from dataset where id in (&list)); disconnect from mysql; quit; %mend; %test(%SUPERQ(list1)); I have tested this solution, and it works fine in SAS9.3 on Windows 7.

SASJedi · ‎10-16-2011

A simpler (and slightly more efficient) way to find the number of numeric variables in a table is to query DICTIONARY.TABLES for the num_numeric column. This has the added benefit in that, if there are 0 numeric columns, the query actually returns a 0 value. Something like this would probably do the trick: proc sql; select num_numeric into :nums from DICTIONARY.TABLES where libname="WORK" and memname="%QUPCASE(&DATASET)") ; quit;

SASJedi · ‎10-16-2011

I really like Fried Egg's approach, but would suggest a couple of small modifications for efficiency's sake: data foo; /* Add end=last to be able to detect the end of the input data */ infile cards truncover end=last; retain mlen_this 0 mlen_that 0; /* To begin with, make the text variables as log as possible */ input wingband this :$32767. that :$32767.; mlen_this=max(mlen_this,length(this)); mlen_that=max(mlen_that,length(that)); if last then do; /* CALL SYMPUTXs need only be executed once, after the last record of the input data is read */ /* Use SYMPUTX instead of SYMPUT to trim leading & trailng spaces from values */ /* Just save the number, don't concatenate any text */ call symputx('mlen_this',mlen_this); call symputx('mlen_that',mlen_that); end; drop mlen:; cards; 1 abcdef ab 2 abc abcd 3 ab abc 4 abcd abcde 5 a a ; run; /* Use the macro variable values to modify the table structure in place */ /* This should be more efficient that re-writing the data set with a DATA step */ proc sql; alter table work.foo modify this char(&mlen_this), that char(&mlen_that) ; quit;

SASJedi · ‎10-11-2011

A more sophisticated macro application with extensive parameter checking would provide protection against this and more... I thought I'd keep it simple as the original poster stated he was just learning SAS, and I didn't want to over complicate the issue. However, something like this would be a good start towards a more robust app: %macro SumNums(DSN_In,DSN_Out, Sumvar); %local Varnames LIB DSN; /* Did the user provide an input dataset name? */ %if &DSN_In= %then %do; %put ERROR: (SumNums) You must provide the name of the data set you wish to process.; %SyntaxHelp: %put NOTE- SYNTAX: %NRSTR(%%SumNums%(DSN_In,DSN_Out,Sumvar%)); %put NOTE- DSN_In =Name of data set to process; %put NOTE- DSN_Out=Name of output data set; %put NOTE- Sumvar =Variable name to hold the sum (default is Total); %put NOTE- Example: %NRSTR(%%SumNums%(sashelp.class,work.test%)); %GoTo EndMacro; %end; %let DSN_In=%QUPCASE(&DSN_In); /* Did the user ask for syntax help? */ %if &DSN_In=!HELP %then %do; %GoTo SyntaxHelp; %end; /* Did the user provide an output dataset name? */ %if &DSN_Out= %then %do; %put ERROR: (SumNums) You must provide the name of the output data set.; %GoTo SyntaxHelp; %end; %let DSN_Out=%QUPCASE(&DSN_Out); /* Is the sum variable given a valid SAS variable name? */ %if &sumvar= %then %let Sumvar=Total; %if not %sysfunc(nvalid(%superq(Sumvar))) %then %do; %put ERROR: (SumNums) %superq(Sumvar) is not a valid SAS variable name.; %GoTo SyntaxHelp; %end; %let DSN=%QSCAN(&DSN_In,2,.); %if &DSN= %then %do; %let DSN=%SUPERQ(DSN_In); %let LIB=WORK; %end; %else %let LIB=%QSCAN(&DSN_In,1,.); proc sql noprint; select name into :Varnames separated by ' ' from dictionary.columns where LIBNAME="&LIB" and MEMNAME="&DSN" and TYPE='num' ; quit; /* Were there any numeric variables in the input data set? */ %if &SQLOBS=0 %then %do; %put WARNING: (SumNums) dataset &DSN_In contained no numeric variables.; %GoTo EndMacro; %end; /* Do the deed! */ data &DSN_out; set &DSN_In; &Sumvar=sum(of &varnames); run; %EndMacro: %Mend SumNums; After compiling, you just call the macro to do the deed. Example of a call for SYNTAX help: %SumNums(!HELP) Example of a call to calculate the total for SASHELP.CLASS: %SumNums(sashelp.class,work.test,MySumVar)

SASJedi · ‎10-11-2011

This is a great place to use a little SQL and macro to more precisely controlwhich variables you sum up later. For my example, I'll use the CLASS data set in the SASHELP library. 1. First execute this SQL code (which can be used as a template) to get a list of all the numeric variables in the target dataset, collected a macro variable named VARLIST: proc sql noprint; select name into :Varnames separated by ' ' from dictionary.columns where LIBNAME='SASHELP' and MEMNAME='CLASS' and TYPE='num'; quit; 2. Next, use the macro variable to write the list for you in the subsequent DATA step code: data want; set sashelp.class; total=sum(of &varnames); run; Partial results below: Obs Name Sex Age Height Weight total 1 Alfred M 14 69.0 112.5 195.5 2 Alice F 13 56.5 84.0 153.5 3 Barbara F 13 65.3 98.0 176.3 4 Carol F 14 62.8 102.5 179.3 5 Henry M 14 63.5 102.5 180.0

SASJedi · ‎10-01-2011

Nice! For efficiency's sake (I know, I know - this data is small, but it's a personal obsession...) consider renaming the date variables during the data step concatenation to avoid executing the COALESCE function in the subsequent merge: data dates; set table2 (rename=(date_1=date)) table3 (rename=(date_2=date) drop=ms); by pid; run; data want ; merge table1 table3 (keep=pid ms) dates; by pid ; run; proc compare data=want compare=table4; run;

SASJedi · ‎09-28-2011

When SYMGET is executing on the remote host and the macro variable was created in the local SAS session, the macro variable you are attempting to retrieve probably does not exist in the remote host symbol table. Check out %SYSLPUT and %SYSRPUT. These statments allow passing macro values between local and remote host, and may help resolve your problem.

SASJedi · ‎09-27-2011

In order to get useful input promptly, it would be helpful if you could include in your post: 1. A small sample of the data you HAVE (generally 3-5 observations is enough) 2. A small sample of the data or output you WANT (again, generally 3-5 observations is enough) 3. A copy of the SAS code you curently have (or the relevant prortions, anyway)

SASJedi · ‎09-25-2011

You can use PROC DATASETS to modify variable characteristics, including formats. Here is some sample code to play with: data test; set sashelp.class; format Name $8. weight z5.1; run; title "Formatted values"; proc print; run; /*Get a list of variable names in a macro variable*/ proc sql noprint; select Name into :Varnames separated by ' ' from dictionary.columns where libname='WORK' and memname='TEST'; quit; /*Remove formats from all the variables*/ proc datasets library=work nolist; modify test; format &varnames; quit; title "After formats removed"; proc print; run;

Online Status	Offline
Date Last Visited	yesterday