About Tom

Tom · ‎10-28-2011

I am not sure I understand this requirement. Are you saying that if a panelist did not report an income for a year you want to treat that as $0? Another method that I like to use that requires fewer copies of the data is create a selection criteria that can be used in a where statement. So for your example I would get a list of id values and use it to subset the data. proc sql noprint ; select distinct id into :id2009 separate by ' ' from have where year=2009; quit; proc means data=have; where id not in (&id2009); var income; run;

Tom · ‎10-28-2011

Test for how to append to empty (zero observation) master datasets and also make sure you know what happens if you do not specify values for all variables. In general you will find that this type of technique is not normally needed (or desired) when processing data with SAS. To me it looks more like the solution that someone would propose that was used to using a language that did not really work on datasets (or just SETS like SQL does). Art's solution using PROC APPEND or other methods to combine your new data into your old data is much more likely to be generally useful.

Tom · ‎10-28-2011

Use the RSASUSER option. You can put it on the command line or add it to you config file. See this thread. http://communities.sas.com/message/105820#105820

Tom · ‎10-28-2011

You could use the INSERT statement in SQL. create table want as &query1; insert into want &query2; insert into want &query3; .... You could instead create views in SQL and then use a DATA step to combine them. DATA steps do not have the same limits. proc sql; create view v001 as &query1; create view v002 as &query2; .... quit; data want ; set v001 v002 .... ; run;

Tom · ‎10-27-2011

Look at the MODIFY statement. proc sql; insert into tb.want (code, age) values (3, 27); quit; data tb.want; modify tb.want; code=3; age=27; output; stop; run;

Tom · ‎10-27-2011

You could use PROC TRANSPOSE. data need ; set have ; _name_ = scan(type_combined,-1,'_'); type=substr(type_combined,1,length(type_combined)-length(_name_)-1); run; proc transpose data=need out=want ; by location type; var population; id _name_; run;

Tom · ‎10-27-2011

Did you try quoting the filename? open(quote(trim(mail_file)),..... Otherwise you will need to create a libref pointing the directory and then try to open the file using the traditional libname.member two level dataset name format. fname=scan(mail_file,-1,'/\'); dir=substr(mail_file,1,length(mail_file)-length(fname)); member=scan(fname,1,'.'); libref='MYDATA'; rc=libname(libref,dir,,'access=readonly'); ** test for error codes ?? ; rc=open('MYDATA.'||member,.....) ...

Tom · ‎10-27-2011

I find boolean flags easier to create and work with. PROC SQL ; create table want as select *,max( st='02' ) as use from have group by acct ; quit; Or a data step using DOW loops data want ; do until (last.acct) ; set have; by acct; use = max(use,st='02'); end; use = max(0,use); do until (last.acct); set have; by acct ; end; run; Or merge it back with itself. data want ; merge have have(rename=(st=st2) where=(st2='02') ); by acct ; if first.acct then use=(st2='02'); drop st2; run;

Tom · ‎10-27-2011

You will need to do code generation. You can use a data step to write code to file and then INCLUDE it. Or use CALL EXECUTE to stuff the code onto the stack. Or write a macro and generate macro calls. My question is what query do you want to generate to get your result. I would assume you want something like. create table want as select * from (select "qry1" as QueryName),(... select statement...) union select * from (select "qry2" as QueryName),(... select statement...) ; Watch out that PROC SQL has a limit on the number of tables that can be referenced in one statement.

Tom · ‎10-25-2011

I know nothing about Web Report Studio, but if you are using PROC REPORT then it has a "friendly" feature of sorting even numeric variables by the character string used to display then unless you tell it otherwise by using ORDER=INTERNAL option in addtion to the ORDER option. define mydate / order order=internal;

Tom · ‎10-25-2011

Now if you want to also order by the date then you will need to first give it real dates and then copy the start and end dates to another variable that you can use to order. data one; format serial 5. date start date9.; input serial start date9. ; date=start; cards; 101 01jan2008 101 01feb2008 101 01mar2008 102 01feb2007 102 01mar2007 102 01apr2007 run; data two ; format serial 5. date end date9.; input serial end date9.; date=end; cards; 101 01jul2007 101 01aug2007 101 01sep2007 102 01may2008 102 01jun2008 102 01jul2008 run; data both ; set one two; by serial date ; run; options nocenter nodate nonumber linesize=50; proc print width=min; run; Obs serial date start end 1 101 01JUL2007 . 01JUL2007 2 101 01AUG2007 . 01AUG2007 3 101 01SEP2007 . 01SEP2007 4 101 01JAN2008 01JAN2008 . 5 101 01FEB2008 01FEB2008 . 6 101 01MAR2008 01MAR2008 . 7 102 01FEB2007 01FEB2007 . 8 102 01MAR2007 01MAR2007 . 9 102 01APR2007 01APR2007 . 10 102 01MAY2008 . 01MAY2008 11 102 01JUN2008 . 01JUN2008 12 102 01JUL2008 . 01JUL2008

Tom · ‎10-25-2011

The basic idea of interleaving records from two (or more) dataset in ID order can by done with SET and BY statements. Did you want to more than that? data one; input serial start $15. ; cards; 101 jan2008 101 feb2008 101 march2008 102 feb2007 102 march2007 102 april2007 run; data two ; input serial end $15.; 101 july2007 101 aug2007 101 sept2007 102 may2008 102 june2008 102 july2008 run; data both ; set one two; by serial; run; Output: Obs serial start end 1 101 jan2008 2 101 feb2008 3 101 march2008 4 101 july2007 5 101 aug2007 6 101 sept2007 7 102 feb2007 8 102 march2007 9 102 april2007 10 102 may2008 11 102 june2008 12 102 july200

Tom · ‎10-25-2011

The _N_ is right, but it does not count observations. It counts interations of the data step. The main point of testing before the input statement is that SAS stops when it reads past the end of file, so the statements following the input statement never execute for an empty input file.

Tom · ‎10-25-2011

So we can simplify the code even more by using the automatic counter _N_, we just need to remember to subtract one for the extra time it starts the step. data _null_; if eof then call symputx('nobs',_n_-1); infile example end=eof ; input; run;

Tom · ‎10-25-2011

I think of it as SAS looking ahead and setting the END= variable before it tries to read the next line. SAS will actually exit the data step at the INPUT statement when it reads past the end of the file. So for a file with N lines the data step starts N+1 times. Here is an example SAS log from reading a three line file. 195 196 data _null_; 197 put 'TOP ' (_n_ eof) (=); 198 infile example end=eof ; 199 input ; 200 put 'BOTTOM ' (_n_ eof) (=); 201 run; NOTE: The infile EXAMPLE is: Filename=...\Temp\SAS Temporary Files\_TD7068\#LN00016, RECFM=V,LRECL=256,File Size (bytes)=9, Last Modified=25Oct2011:12:32:57, Create Time=25Oct2011:12:32:57 TOP _N_=1 eof=0 BOTTOM _N_=1 eof=0 TOP _N_=2 eof=0 BOTTOM _N_=2 eof=0 TOP _N_=3 eof=0 BOTTOM _N_=3 eof=1 TOP _N_=4 eof=1

Online Status	Offline
Date Last Visited	Monday