About Tom

Tom · ‎09-23-2011

All of the sessions need use -RSASUSER. The first one has the PROFILE open so the second one cannot open it to write. With the -RSASUSER option SAS will not attempt to open it to write and it will work.

Tom · ‎09-23-2011

If the macro variable already exists the %GLOBAL does not change its value. In this piece of code the intent was to initialize the variable so that will have a value even if SQL did not find any observations. So the side effect would be that the pre-existing value persists even after this block of code runs. The %LET method does not care whether the variable is local (to the executing macro) or external (global or local to a surrounding macro scope) and so is more flexible. I do not like about overuse of %GLOBAL is that it clutters the global macro space. I write a lot of utility macros and I am careful to make sure to define the inputs and outputs to avoid unintended interactions. Within a macro I will frequently use %LOCAL to define (and initialize to empty) a variable.

Tom · ‎09-23-2011

You can prevent this by controlling your access to the SASUSER library. What I usually do is add the -RSASUSER option to my configuration file. This will make SAS open the SASUSER library in readonly mode and so it will not lock out the second (or third or fourth) session from accessing the PROFILE catalog. You can add this to either the configuration file or to the command line that you use to start SAS. The only trouble I have had is the occasional SAS tool that doesn't seem to understand that he RSASUSER option exists and is hard coded to write files to the SASUSER library. When you actually do want to modify something in your PROFILE then use the -NORSASUSER option. Another way is to create multiple directories with copies of your sasuser directory and use the -SASUSER option to point to different ones for different jobs. This has the disadvantage of requiring you to synchronize them.

Tom · ‎09-22-2011

What you experienced is most likely because the query did not match any observations. If you use SQL to create a macro variable and the query does not match any records then SQL does not create the macro variable. I usually just use a %LET before the query to set the value that I want if the query does not macth any observations. Using %global has other side effects, the worst being errors when the requested macro variable name already exists in a currently active local scope. proc sql noprint ; %let datebig=; select max(age) into : datebig from sashelp.class ; quit;

Tom · ‎09-20-2011

You should have also seen a message that the macro variable TABLE_LIST was not defined. This is because the query did not find any datasets that matched your query and so TABLE_LIST will never get defined. Most likely it is because of your use of lowercase strings. I know for sure that the LIBNAME value will always be uppercase. I think that the MEMNAME value will also always be in uppercase. Why not just start by listing out what tables SAS does show in the DICTIONARY.TABLES view? proc print data=dictionary.tables; var libname memname ; run; You also need to modify the catcatenation of libname and membername to eliminate trailing spaces on the LIBNAME variable. %macro merge_multiple; %let table_list=; proc sql noprint; select catx('.',libname,memname) into : table_list separated by " " from dictionary.tables where libname eq "WORK" and memname like 'DATA%'; quit; data work.want; %if &sqlobs %then %do; set &table_list; %end; run; %mend merge_multiple; data work.data1 ; ds=1; run; data work.data2; ds=2; run; %merge_multiple;

Tom · ‎09-20-2011

Would need to know more about the coding scheme (or least a few more data points) but it looks like your number 110272 could be split into two pieces. 110 represents the year (add it to 1900) and 272 represents to number of days. 281 data _null_; 282 x=110272; 283 sas=mdy(1,1,1900+int(x/1000)) + mod(x,1000) -1 ; 284 diff = x - sas; 285 put x= sas= diff= sas date9.; 286 run; x=110272 sas=18534 diff=91738 29SEP2010

Tom · ‎09-20-2011

Why would you expect a dataset from the SASHELP library to necessarily exist in the PROJECT library? Change the query to match your situation. This is the clause that needs to change: where libname='SASHELP' and memname like 'CL%' You should change SASHELP to PROJECT to search for members in the PROJECT library. You should also change the rest of the conditional to find the particular datasets that you want. For example if you want to find every dataset in project then just leave it at: where libname='PROJECT'

Tom · ‎09-20-2011

Hopefully you have your answer with the INTCK function. In fact you should be able to get the number you want just from the SQL query. PROC SQL; SELECT inctck('QTR',MIN(enquiry_date),today()) INTO :N FROM sample; QUIT;

Tom · ‎09-20-2011

Try adding a WIDTH option to make the column wide enough for your longer title. You might also check what character you are using for you split character, but normally you would not use a space for that.

Tom · ‎09-20-2011

Depends on what you mean by "import". If you just have text files then I would just "input" them and would not bother with using any import functionality. 1) If the file names follow a simple pattern then just us a wildcard in the filename specification. data all ; infile '*.txt'; input column1 ; run; 2) If you have a static list of filenames then build a FILENAME statement. filename myfiles ('one.txt','two.txt,'three.txt','four.txt','five.txt'); data all; infile myfiles; input column1; run;

Tom · ‎09-19-2011

Why not generate the "title" lines in the report using ODS TEXT statements? I am not sure how that would interact with panels.

Tom · ‎09-19-2011

One way is to merge the two by MEMNO and compare the values. Because the variables have the same name in both dataset you will need to rename one set of names so that you can have two different variable names to compare. One way to avoid having to type all those new names is to create a dataset that looks like the history file, but has the address information from the master file. Then you could use PROC COMPARE to compare the two. data check ; merge demo demohistory(keep=memno) ; by memno; run; proc compare data=check compare=demohistory ; id memno; run;

Tom · ‎09-19-2011

You need to add a SEPARATED BY ' ' claus to the INTO cluase of the select statement. Otherwise SQL will only put the value from the first observation it pulls into the macro variable.

Tom · ‎09-19-2011

I think that you are asking if you can loop over the formatted values of variable in macro code. In general the answer is no, but there have been many discussions about ways to work around this. Here is one from earlier this year. http://listserv.uga.edu/cgi-bin/wa?A2=ind1103C&L=sas-l&D=0&P=24621

Tom · ‎09-19-2011

As data_null_ pointed out your ordering in your SQL select statement and your PROC REPORT are not the same. In SQL you order by: region,ordsale desc In your COLUMN statement you order by: region agent_info sale ordsale Try changing your COLUMN statement to : region ordsale agent_info sale

Online Status	Offline
Date Last Visited	yesterday