If you want to use these macro variables for anything other than human-readable display, then no formatting is needed. The raw numbers work perfectly well in code. %let today = %sysfunc(mdy(1,1,2023)); %let curr_year = %sysfunc(year(&today.)); %put &curr_year;   ... View more

@sajjad1122 I really like Python but I can't see the point to use it for comparing SAS datasets given there is Proc Compare which does the job. And unlike with Python Proc Compare will compare the actual original SAS data sets and not data that first got converted to data frames. ... View more

Here is an example that is similar to what you are trying that illustrates the timing issue of trying to issue macro code via CALL EXECUTE. data class; set sashelp.class; run; data _null_; call execute('libname source (work);'); call execute('%put not quoted=%varexist(source.class,sex);'); call execute('%nrstr(%put) nrstr quoted=%nrstr(%varexist)(source.class,sex);'); call execute('libname source clear;'); run; So we are trying to run code that  1) Sets up a libname. 2) Checks for the existence of a variable in a dataset in that libname. 3) Clears the libname.   The example has two different methods of coding the second item.  One with no macro quoting.  And one with %NRSTR() wrapped around each macro function or macro statement or macro call.   Here is the resulting SAS log 1 data class; set sashelp.class; run; NOTE: The data set WORK.CLASS has 19 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 2 data _null_; 3 call execute('libname source (work);'); 4 call execute('%put not quoted=%varexist(source.class,sex);'); 5 call execute('%nrstr(%put) nrstr quoted=%nrstr(%varexist)(source.class,sex);'); 6 call execute('libname source clear;'); 7 run; not quoted=0 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: CALL EXECUTE generated line. 1 + libname source (work); NOTE: Libref SOURCE was successfully assigned as follows: Levels: 1 Engine(1): V9 Physical Name(1): (system-specific file/path name) 2 + %put nrstr quoted=%varexist(source.class,sex); nrstr quoted=1 3 + libname source clear; NOTE: Libref SOURCE has been deassigned. As you can see the %PUT without the macro quoting ran while the DATA _NULL_ step was still working and since the libref SOURCE had not yet been defined the %VAREXIST() function call did not find the variable.   But the second one ran after the data step.  And after the LIBNAME statement so it did find that SEX existed in SOURCE.CLASS.  Plus you can see the actual source code that CALL EXECUTE() was able to push onto the stack to run after the data _NULL_ step.   ... View more

Hello,   I did some test with sashelp.class and you inlist and inbase statement and it seems to do what I am expecting to get. Regards,   data test1; rownumber=_N_; set sashelp.class; run; /* Creating the synthetic masterlist using test1 and applying the following criteria (content of test1 minus people who's age eq 12 years old */ data masterlist; set test1; where age ne 12; run; data test2_org; set test1; where sex eq 'M'; run; /*data test2 test2_dr;*/ /* merge test2_org (in=inbase)*/ /* masterlist (in=inlist)*/ /* ;*/ /* by rownumber;*/ /* if inlist and inbase then output test2_dr;*/ /* else if inbase then output test2;*/ /*run;*/ /* so as my master list contains many policies per filename, the idea is to create a subdataset just with the filename and path. The make a call execute with the path and filename then execute the last sas code */ data pathNfilename; input libname1 :$10. libname2 :$10. fname :$10.; datalines; source1 dest1 test2 ; run; data _null_; set pathNfilename; call execute (compbl(cat( '%nrstr(libname ',strip(libname1),' "%sysfunc(pathname(work))"; )', '%nrstr(libname ',strip(libname2),' "%sysfunc(pathname(work))"; )', "data ",strip(fname)," ",strip(fname),"_dr;", "merge ",strip(fname),"_org (in=inbase) ","masterlist (in=inlist);", "by rownumber;", "if inlist and inbase then output ",strip(fname),"_dr;", "else if inbase then output ",strip(fname),";run;" ))); run; ... View more

I think your actual problem is simply that you told SAS to treat the LF as part of the data instead part of the end of line markers by using TERMSTR=CR instead of TERMSTR=CRLF on the infile statement.   But your post has such a collection of many of the common things on my pet peeves list of SAS coding issue that I would like to go through it and explain.  It has nothing really to do with your question (or really whether the code will work, there are always lots of different ways to do things in SAS).  It is just that these particular things annoy me.   Do not use the old CALL SYMPUT() method instead of the newer CALL SYMPUTX() method unless there is really a need to store leading and/or trailing spaces into the macro variable. call symputx('csvFile',Filename); call symputx('cn_csvFile1',infile_name);   Do not use the old MISSOVER option on the INFILE statement instead of the TRUNCOVER option unless it is really important that values at the end of lines that are shorter than the informat asks for be discarded instead of being used as is.  Since your data step is using only LIST MODE input the informat will never be longer than the value available, but why risk it?   Why bother to attach FORMATs to character variables?  SAS can display character values just fine without them and you do not run the risk of accidentally at some later point after changing the length of the variable ending up with a display format that truncates the value.   The DATETIME format has a bug so you should never use the width of 18 with it.  That will just generate the same string as DATETIME16. does.  If you want four digit years you need to use at least a width of 19 with DATETIME format.   If you have attached an informat to the variable then there is no need to again state the informat to use in the INPUT statement.  This means the INPUT statement can be much simpler.  And for this type of program if you define the variables in the order they appear in the text file (like your program does) then the you can even use a positional variable list in the INPUT statement. INPUT POLICY_NUMBER -- CONTRACT_UUID ; If you want to remove the leading spaces from a variable just use the LEFT() function.  Using STRIP() to also remove the trailing spaces and then immediately storing the value back into the same fixed length variable will just cause the trailing spaces to be added back since SAS pads the values with spaces. policy_number=left(policy_number); This is actually a place where your code might be wrong.  If the issue was that there was some invisible character in the front of POLICY_NUMBER why did you remove the leading spaces?  Isn't it possible that the invisible character was a space?     ... View more

Do you really need to create a physical copy of the uncompressed file just to READ it? Why not just read the output of your uncompress -c command directly.   Try looking at the first few lines of the file to make sure the uncompress is working and you know what the file has in it. %let path=/dwh_actuariat/sasdata/Data_Retention/sas2002/be; data _null_; infile "uncompress -c &path./bel.prod0110.se8030ac.m525.dat.Z" pipe obs=5; input; list; run; Once you can see the file then you can write a data step to read it. data np; infile "uncompress -c &path./bel.prod0110.se8030ac.m525.dat.Z" pipe; length var1 $20 var2 8 .... varlast 8 ; input var1 -- varlast; run; If you must GUESS how to read the file then remember that PROC IMPORT is not able to read from a PIPE (why I have no idea).  So you could either use some other tool for GUESSING how to read the file such as : https://github.com/sasutils/macros/blob/master/csv2ds.sas  Or just write your own data step to read it.   If you need to make a temporary permanent file then perhaps just use a data step to copy the lines. filename tempdat temp; data _null_; infile "uncompress -c &path./bel.prod0110.se8030ac.m525.dat.Z" ; file tempdat; input; put _infile_; run; proc import file=tempdat dbms=csv out=np replace; run; Also you appear to be trying to tell PROC IMPORT to read the file as if it was a CSV file that used space as the delimiter.  Normally files that use spaces as the delimiters are not really delimited files.  Instead they normally use the multiple spaces when values are short so that the actual values appear in columns.  So trying to read such a file as a delimited (csv) file will general cause mistakes.   If your code was supposed to be indicating that TAB (or some other invisible character) is the delimiter then use DELIMITER='09'x (or whatever hexcode is appropriate) instead of trying to figure out to insert and actual tab character inside quotes into your program file.  That is very likely to be misunderstood by humans.  And editors are likely to replace the tab with spaces any way.   ... View more

This looks like an unusually complex approach.  I enjoy the macro language, and CALL EXECUTE, but I think it's rare to use CALL EXECUTE to execute the code to define a macro. In this case, when the DATA step runs it will generate code to create the macro LOOKUPTBL.  The macro itself has no  parameters but in a sense it's dynamic because you're generating the macro source code dynamically.  If there are 10 records in work.inforcelisting5 you'll create the macro LOOKUPTBL 10 times, and each time it could have a different definition.    I would think this would be easier to design, debug, and maintain if you just define a macro with a few parameters:   %macro lookuptbl (data=,varname1=, varname2=, varname3=); ... %mend; With that approach you could still use CALL EXECUTE to generate the macro calls, and you could use if VAREXIST to decide which variable names to pass into each macro call.   Or if you want to leave the variable exists tests in the macro, you could do it like: %macro lookuptbl(data); %local varname1 varname2 varname3; %if %varexist(&data,police) %then %let varname1=police; %else %if %varexist(&data,agreement_nbr) %then %let varname1=agreement_nbr; *...; %put &=varname1 &=varname2 &=varname3; proc sql; create table table_info as select distinct strip(a.&varname1.) as agreement_nbr length=20 ,strip(cie) as cie length=2', /* ... */ ; quit; %mend lookuptbl; The idea of dynamically generating macro definitions is kind of scary to me, but I'm sure people do it. Usually macros are dynamic enough, especially when paired with using CALL EXECUTE or similar to generate macro calls.   ... View more

Since you know the value of CIE in the data step use an actual IF statement to test it and then just generate the SQL code you want.  So split your one CALL EXECUTE() statement into three instead of just one. ... call execute(....); if cie='be' then call execute('"B"'); else if cie='qc' then call execute('"A"'); else if cie='gp' then call execute('"P"'); else call execute('a.company_cd'); call execute(...); ...   ... View more

First thing is there is NO need to redefine the macro over and over.  Just make it an test it to make sure it works.  Then your code generation step can use it with confidence.   Here is a simplified example (I have not idea what you intend to do with that CIE variable and how it is supposed to impact the choice of variable name to use). %macro lookupname(dsname); %if not %symexist(varname1) %then %global varname1; %let varname1="No variable found"; %if %varexist(&dsname,police) %then %let varname1=police; %else %if %varexist(&dsname,agreement_nbr) %then %let varname1=agreement_nbr; %mend; Let's try it on SASHELP.CLASS and see if it works. 8 %lookupname(sashelp.class); 9 %put &=varname1; VARNAME1="No variable found" Now let's try it on a dataset that does have POLICE variable. 11 data class; 12 set sashelp.class; 13 police=sex; 14 run; NOTE: The data set WORK.CLASS has 19 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 15 16 %lookupname(class); 17 %put &=varname1; VARNAME1=police I find it is MUCH easier to just use the PUT statement to generate the code to a FILE instead of using CALL EXECUTE().   filename code temp; data _null_; file code; set .... ; put ..... ; run; That will let you take advantage of the power of the PUT statement (and SAS formats).  You won't have to worry about the macro processor interpreting the text before it can get executed (so no more %NRSTR() functions needed).  But it also means you have something you can LOOK and make sure the logic for generating the code is working. data _null_; infile code; input; put _infile_; run; So let's try your example program.  First let's make a driver dataset that will point to the CLASS dataset we made above that does have a POLICE variable. data inforcelisting5; length fname $32 cie $8 engine $10 path $200; fname='class'; path=pathname('work'); output; run; So let's just make a temporary code file. filename code temp; data _null_; file code ; set inforcelisting5; put 'Libname dssource ' engine path :$quote. ';' / '%lookupname(dssource.' fname ');' / '%put &=varname1;' / 'proc sql;' / 'create table table_info as' / ' select distinct &varname1. as agreement_nbr length=20' / ' from dssource.' fname 'a' / ';' / 'quit;' / 'libname dssource clear;' ; run; Which will generate this code. Libname dssource "..." ; %lookupname(dssource.class ); %put &=varname1; proc sql; create table table_info as select distinct &varname1. as agreement_nbr length=20 from dssource.class a ; quit; libname dssource clear; So let's run it. 850 %include code / source2; NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname). 851 +Libname dssource "..." ; NOTE: Libref DSSOURCE was successfully assigned as follows: Engine: V9 Physical Name: (system-specific file/path name) 852 +%lookupname(dssource.class ); 853 +%put &=varname1; VARNAME1=police 854 +proc sql; 855 +create table table_info as 856 + select distinct &varname1. as agreement_nbr length=20 857 + from dssource.class a 858 +; NOTE: Table WORK.TABLE_INFO created, with 2 rows and 1 columns. 859 +quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 860 +libname dssource clear; NOTE: Libref DSSOURCE has been deassigned. NOTE: %INCLUDE (level 1) ending. And here is our resulting dataset   ... View more

@alepage wrote: I have declare the varname and varname2 as global.  Then I don't have any warning.  But I still have the issue with varname which is always blank. From the logic of your macro that means that none of the conditions were met. Both of the two variables you looked for with %VAREXIST() were not found and the CIE value was not be.   Run it first without all of the complications and make sure it works.  First find the value of FNAME and ENGINE and PATH and CIE you used from your dataset.   Then use them to generate the LIBNAME statement and the two %VAREXIST macro calls. For example if ENGINE as empty and PATH as /home/myname/mydir and the value of FNAME as dssource.fred then run code like libname dssource '/home/myname/mydir'; %put %varexist(dssource.fred,police);   Notice that for your logic to make any sense the value of FNAME in the dataset has to include the libref DSSOURCE that you defined with the LIBNAME statement.  If the value of FNAME was just FRED then your call to %VAREXIST() would need to include the dssource. before the value of FNAME.   Also you can run %IF/%THEN logic in open code (without defining a macro) as long as you use %DO/%END blocks and do not attempt to nest them.   ... View more

@alepage wrote: Hello, I am using a proc sql because I need to make an inner join from a policies list table.  So, imagine that police is the variable name into some datasets and for other dataset it will be agreement_nbr, does the coalescec will work or not?  If not, what will be a work around solution. Know what variable to use before you write the code.   Or write some code that can check and then generate the right code based on which variable(s) exist.  Perhaps a macro, but also you could do it with CALL EXECUTE() if you wanted, just like you mentioned in your topic. ... View more

As I look at this again a better regex would be:   /((jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)(\d{4}))/i Which means: Match exactly one of the twelve alternates for month abbreviations followed by 4 digits (0-9, represented by \d directive) case-insensitive (the /i modifier) The regex I supplied in my original answer works, but the above is more concise and limits the match for month name to "exactly one" instead of "one or more" (which is what the + modifier does). ... View more