The question might be are there are any periods that you want to keep in that field.   Perhaps: data test; last_name = 'stanley jr.'; last_name = compress(last_name,'.','t'); run; which will remove all periods in the field.   Data design note: JR, SR, II, III, IV, 'the third'  and such are not really part of a last name field and perhaps should be in a separate field. ... View more

Post Example data Code Log It is a real pity and a PITA that we have to tell you this AGAIN AND AGAIN. ... View more

If you just need individual csv files and not generate individual datasets, the following will do it. Note, names with embedded spaces will have the blanks removed   proc sql noprint; select distinct make into :carlist separated by '|' from sashelp.cars where make is not null; ; quit; %macro each; %local i next_car; %let i=1; %do %while (%scan(&carlist, &i) ne ); %let next_car=%scan(&carlist,&i, |); proc export data=sashelp.cars(where=(make="&next_car")) outfile="~/csv/%sysfunc(compress(&next_car)).csv" dbms=csv replace; run; %let i = %eval(&i + 1); %end; %mend; %each;   ... View more

Macro variables containing date values, date/time values or time values should NOT be formatted (Maxim 28) for arithmetic or logical operations.   data _null_; call symputx('tday',today()); run; %put &tday ; data test; my_date = INTNX('MONTH',&tday,1,'SAMEDAY') ; format my_date mmddyy8.; run; Why? Because the INTNX function cannot work with &tday as you have created it, because the value 02/18/22 is interpreted as 2 divided by 18 divided by 22. However, the unformatted value of &tday (as I have programmed it) is 22694, which is indeed exactly what INTNX needs, this is interpreted as a valid legal SAS date. (Valid legal SAS dates are the number of days since 01JAN1960, that's what 22694 represents).   Please, for clarity, you do not have a macro in your code. You have a macro variable. These are not the same. Please do not refer to macro variables as macros or vice versa, this is incorrect. Clarity is always good. Adding: it is not the fault of the macro variable here, as your title implies ... it is a programming error. A good method to use is to first write code without macro variables and get that to work properly, and then creating code that works with macro variables should be relatively easy. Most people don't do this, and they should! But if your code doesn't work without macro variables, then it will never work with macro variables.    ... View more

Please post all relevant statement, including the statement setting "filehere". An idea to fix this: data _null_; length csvFile $ 200; do code = '01, '02', '03'; csvFile = cats("&myPath/&ThisStateID/&ThisStateRpt.&Currdate_Key._", Code, ".csv"; if fileexist(csvFile) then do; call symputx('csvFile', csvFile); call symputx('filehere', 'Y'); leave; end; end; run; %if &filehere. = Y %then do; data ...; infile "&csvFile" ....; ... run; %end; %end; ... View more

The desire is a csv file however it saves as an excel. I dont know why Your code creates a csv file, but Windows thinks all csv files are meant to be opened with Excel. That's all. Make it a habit to set Windiws Explorer to show all files all directories all file extensions so that you see all pertinent information for a file, regardless of its association. ... View more

I don't understand what the question is.  You can definitely be connected to multiple external databases at the same time. proc sql; connect to a; connect to b; create table c as select * from (select * from connection to a (select * from table_in_a)) a left join (select * from connection to b (select * from table_in_b)) b on a.id = b.id ; quit; But I have a feeling you are trying to ask a different question. ... View more

Quite obvious: you want to read a time, and the length of the string is 8, so try TIME8. ... View more

Your code IS showing the actual date.  You gave it a number of seconds that was under 3 hours into the first day of 1960 so it is properly showing the date as the first of January in 1960.  SAS stores dates as number of days since  1960 and datetime values as number of seconds since 1960.   If you want the DATETIME of execution use the DATETIME() function.   I don't recommend displaying dates in either MDY or DMY order, either one will confuse 50% of your audience. Either use YMD order of d-MON-y. 312 data now; 313 today = date(); 314 now = datetime(); 315 put 'TODAY ' / today comma12. / today date9. / today yymmdd10. / today mmddyy10. / today ddmmyy10. 316 // 'NOW ' / now comma17. / now datetime19. / now mdyampm. / now e8601dt. 317 ; 318 run; TODAY 22,629 15DEC2021 2021-12-15 12/15/2021 15/12/2021 NOW 1,955,222,463 15DEC2021:21:21:03 12/15/2021 9:21 PM 2021-12-15T21:21:03   ... View more

Are you using an Oracle DB? Can you use explicit sql instead? In that case your SQL is passed directly to the server so you can use the Oracle SQL code. ... View more

  The default format SAS will use to display a number when you haven't told it do something else is BEST12.  Your number as nore than twelve digits, hence the use of scientific notation to represent it as best it can be in only 12 characters.   If you know the longest digit strings you have in that field are 13 characters then just tell SAS to use a different format to display the values. data have; acct_number = 1000283639994; format acct_number 13.; run; What do you mean by "import"? If the original file is just a text file, like a CSV file, then read this variable as character to begin with in addition to not having to worry about what format is used to display it you won't have to worry about losing data if the number of digits exceeds the maximum that floating point numbers can represent without having gaps.   ... View more