About Tom

Tom

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

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

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

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

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

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

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

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

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.

Tom

To see why you got that message turn the MPRINT option before running the macro. You should see that you generated lines like: IF est = "est" THEN flg_est = 1; Which to the data step compiler means you want to compare the value of the variable named EST to the string literal "est". Just add quotes around the value in your macro code: IF "&sheet." = "est" THEN flg_est = 1; So that it generates this SAS code: IF "est" = "est" THEN flg_est = 1

Tom

Haven't used JCL in over 20 years, but SAS has had major enhancements in that time so perhaps you could use SAS code to do what you want? For example can you use the FILENAME= option on an INFILE statement to figure out want the DSN= value was in the JCL DD statement?

Tom

You don't need to two passes through the data to calculate the maximum number of words and the maximum word length. And note that double quotes should not be considered a delimiter. *Get the max number of words and length of the longest word; data _null_; set have end=last; retain length 1 max 1; n=countw(HCPCS_CD,',[] ','q'); max=max(n,max); do i=1 to n; length=max(length,lengthn(dequote(scan(HCPCS_CD,i,',[] ','q')))); end; if last then do; call symputx('length',length); call symputx('max',max); end; run; %put &=max &=length;

Tom

So you have two DATETIME values (not DATE values). Which helps because the difference between two dates would be in DAYS and it would be impossible to detect differences of less than 24 hours. But the difference between two datetimes is in SECONDS. So you can use the TIME format to display them. Make sure the width is long enough to properly show the maximum duration. The default width of 8 only has room for two digits for the houts, so can only handle durations that are less than 100 hours. data have; input (TSP_DEB TSP_MOF) (:datetime.) ; duration = TSP_MOF - TSP_DEB ; format TSP_DEB TSP_MOF datetime25.6 duration time20.6; datalines ; 24SEP2025:15:13:35.000000 24SEP2025:15:21:00.000000 24SEP2025:15:13:35.000000 25SEP2025:15:21:00.000000 ; proc print; run;

Tom

You will probably need to write your own code for this. SAS dataset do not have arrays. The ARRAY statement is just something you use in a data step to allow you to reference an actual variable via an index into a list. You will need to define the lengths of the variables you want to create and the number of them. So something like this: data sas.dataset; set snow.table ; array HCPCS_CD[4] $5 ; do index=1 to min(dim(hcpcs_cd),countw(hcpcs_code,'[,]')); hcpcs_cd[index] = dequote(scan(hcpcs_code,index,'[,]')); end; drop index; run;

Tom

No, because both SAS and your operating system will cache the file. So the second SET will get its observation from the cache most of the time. And when it doesn't the the next time the first SET runs it will find the observation in the cache.

Online Status	Offline
Date Last Visited	yesterday