About Tom

Tom · ‎10-01-2025

@sc5 wrote: Thanks for replying! Every script has that piece of code in it. So the main script is like %include "mydir/report_1.sas"; %include "mydir/report_2.sas"; %include "mydir/report_3.sas"; and each of those scripts has the code snippet in them. Is "mydir" a fully qualified path? For example if SAS is running on Unix does it start with / ? Does the path mentioned exist on the machine where SAS is running? Does the user that is being used to run the job have permission to read the SAS programs?

Tom · ‎10-01-2025

You have a typo (or perhaps I did?). Notice that SAS tells you about it: NOTE: Variable nobs is uninitialized. Fix this line: if 0 then set fixed4interleave nobs=nob;

Tom · ‎10-01-2025

So check that also. data want; if 0 then set one nobs=nobs; merge one(keep=id in=in1) two(keep=id in=in2); by id; if first.id; do until (last.id); set one two; by id; if (in1 or 0=nobs) and in2 then output; end; run; If the first dataset is a view that prevents NOBS from working then just add an extra step to set a macro variable instead. data _null_; call symputx('nobs',0); set one; call symputx('nobs',1); stop; run; data want; merge one(keep=id in=in1) two(keep=id in=in2); by id; if first.id; do until (last.id); set one two; by id; if (in1 or 0=&nobs) and in2 then output; end; run;

Tom · ‎10-01-2025

Perhaps you are trying to only write observations for BY groups that appear in both? data want; merge one(keep=id in=in1) two(keep=id in=in2); by id; if first.id; do until (last.id); set one two; by id; if in1 and in2 then output; end; run;

Tom · ‎10-01-2025

Your description is too short to understand exactly what you mean. And I don't understand what your posted data step is trying to do. To interleave two datasets just use code like: data want; set one two; by id; run; What is it that you want to do differently than that? If you want to exclude BY groups from TWO that do not appear in ONE then add a retained variable to remember if there were any observations for that group in ONE. data want; set one(in=in1) two(in=in2); by id; if first.id then any=0; retain any; if in1 then any=1; if in2 and not any then delete; drop any ; run;

Tom · ‎09-30-2025

An array would only be useful if you have multiple variables you wanted to reference. But that is not what I took your statements to mean. Instead if sounds like you have just TWO variables, both of which have space delimited lists of words. data have; infile cards dsd truncover ; input (a b) (:$50.); cards; VAR1 VAR2 VAR4,VAR3 VAR4 x y z,a X c ; So make a new variable (or just update one of the existing ones). data want; set have; length all $100 ; all=a; do i=1 to countw(b,' '); if 0=findw(all,scan(b,i,' '),' ','i') then all=catx(' ',all,scan(b,i,' ')); end; drop i; run; Result But it would be simpler if your data structure just had one word per observation. data have; input type :$10. name :$32. ; cards; a VAR1 a VAR2 a VAR4 b VAR3 b VAR4 ; proc sql; create table want as select distinct name from have ; quit;

Tom · ‎09-29-2025

In that case go back to Activity 2.04 and redo it and make sure it works and you have saved the libname.sas program (and you know how to find it again).

Tom · ‎09-29-2025

Are you really running SAS on Windows such that C: makes sense? If you are running on Unix you want to start your path with the root node (the / ). LIBNAME mydata "/home/u63993525/EPG1V2/data"; Do you have some way you can check if the directory actually exists on the machine where SAS itself is running? Note if you are using SAS/Studio or Enterprise Guide to write and submit your SAS programs then SAS itself is most likely running on a different machine than the one you are using directly. Also remember that on Unix case of letter matter in filenames. So these are all different paths: /home/u63993525/EPG1V2/data /home/u63993525/EPG1V2/Data /home/U63993525/EPG1V2/data /home/u63993525/epg1v2/data

Tom · ‎09-29-2025

That macro does not look like it will generate valid SAS code. You are generating a data step that looks like: data want; infile one ; infile two; ... infile last; input; run; If you want to read from multiple files you need to either run multiple data steps. Or put the INPUT (and OUTPUT) statements inside of a loop so you can read all of the lines from the file in one pass of the data step. But in that case you no longer need the %DO loop so there is no need to bother creating a macro. * Assume there exist macro variables named NUM_OBS and FILE1 ... FILE&num_obs ; data ingest_data; length filename $300 ; length field1-field3 $50 ; do i=1 to &num_obs; filename = symget(cats('file',i)); infile csv filevar=filename dsd firstobs=2 truncover encoding="wlatin1" end=eof; do while (not eof); input field1-field3; output; end; end; drop i; stop; run; And you probably do not need the macro variables either since I assume you created them from a dataset. So just use the dataset to drive the loop. Say you have a dataset named LIST with a variable named FILENAME. * Use a dataset to secify the list of files ; data ingest_data; set list; length field1-field3 $50 ; infile csv filevar=filename dsd firstobs=2 truncover encoding="wlatin1" end=eof; do while (not eof); input field1-field3; output; end; run; Another method is to use the %DO loop to generate a FILENAME statement that creates a FILEREF that points to all of the files. filename csv ( %do i = 1 %to &num_obs; "&&file&i" %end; ); Then you can use that fileref in your data step. But this time use the FILENAME= option to have SAS populate a data step variable that indicates the current file being read so you can detect the and skip the header lines. data ingest_data; length filename $300 ; length field1-field3 $50 ; infile csv filename=filename dsd truncover encoding="wlatin1" end=eof; input @; if filename ne lag(filename) then delete ; input field1-field3; run;

Tom · ‎09-29-2025

Just some comments on the example SAS code you posted. If you want to set a macro variable you don't need to run a data step; %let imsid=; You almost never want to use the extremely ancient MISSOVER option or the merely ancient CALL SYMPUT() method. Instead use the modern TRUNCVOER option and the CALL SYMPUTX() method. The only reason to use MISSOVER is if you want INPUT to ignore text at the end of line that is too short for the informat width being used. The only reason to use CALL SYMPUT() is if you want the generated macro variable to have leading and/or trailing spaces. The reasons that example would work using those ancient options/methods is 1) Because you are reading from a card deck the data lines will contain 80 characters even when the number of non blank characters is less than the 14 bytes you are reading. 2) Your usage of the IMSID macro variable most not mind the extra 10 spaces that you stored into the value by using CALL SYMPUT() with a character variable defined to store 14 bytes. Of course you could also use the ancient NAMED INPUT style of reading data. /* Initialize IMSID macro variable */ %let imsid= ; /* Read IMSID from SETUP */ data _null_; infile setup ; input @'IMSID=' imsid :$4.; call symputx('imsid',imsid); run;

Tom · ‎09-28-2025

Note it is much easier to do in normal SAS instead of SQL. data want; merge sq.merchant sq.transaction(in=in2); by merchantid; if not in2; keep merchantname merchantid type zip; label merchantname ='Merchant Name' merchantid='Merchant ID' type='Merchant Type' zip='Merchant Zipcode' ; run;

Tom · ‎09-28-2025

When you do a LEFT join it means that all observations from the LEFT table will make it into the intermediate results. And when there is an observation that did not match any observation from the RIGHT table then all of the variables contributed by the RIGHT table will have a missing value. That is why the WHERE condition works. Personally I find your answer much easier to follow. But if you need multiple variables to perform the join then you cannot use the IN operator.

Tom · ‎09-27-2025

Sound like you want to use UPDATE and not MERGE. List the dataset you want to "WIN" last. data aa; input CustID y; cards; 111 . 123 35 222 20 444 50 555 70 ; data bb; input CustID y; cards; 111 15 123 27 222 20 333 35 444 . 666 80 ; data want ; update bb aa; by custid; run; proc print; run; Note that for both the datasets need to be sorted by the BY variable(s).

Tom · ‎09-27-2025

If you want to know which value of VISIT the counts are for then extract VISIT also. proc sql noprint; select count (distinct ID) , visit into :sam1- , :visit1- from ds where group=1 group by visit ; %let nvisits=&sqlobs; quit; I

Tom · ‎09-27-2025

The purpose of the Q modifier is to treat delimiters inside of quotes as normal characters. But if the double quote character is one of the delimiters then stings quoted with double quotes would look like separate words. Meaning you would need to use single quotes to mask delimiter characters. But you might want to actually test it as SAS's implementation may not use the same order of operations ( between checking for quoted strings versus checking for delimiters) as I would have done. 26 data test; 27 input string $40.; 28 without = scan(string,2,'[ ,]','q'); 29 with = scan(string,2,'[" ,]','q'); 30 put (w:) (=); 31 cards; without="2 x" with="2 x" So it will treat the matched quotes as indicating one word. But the result also shows that adding double quote as a delimiter is not needed.

Online Status	Online
Date Last Visited	Saturday