Hello, I would like to put a where statement to reduce the size of one of the dataset. First question is it possible to do it and if so how?     data dest1.&filename dest2.&filename._Anonym; merge source4.&filename. (in=inbase) src1.**bleep**rmt_in_premds_intg_20241031 (in=inlist) (where=(filename eq "&filename.")) ; by ORGNL_SAS_DS_ROW_NBR; if inlist and inbase then output dest2.&filename._Anonym; else if inbase then output dest1.&filename.; run; ... View more

Hello,   I am using the following SAS code to search for premium datasets from a policies list.  So if I have a policy number let's say 1111-1111, it search for this policy into all datasets available.   However, in the on condition, we have    on(a.agreement_nbr=b.agreement_nbr_dr and a.&varname1.=b.province and a.company_cd=b.legacy_underwriting_company);  In some old premium datasets, a.&varname1 eq 'PQ' and in b.province = 'QC', so we are loosing 4772 policies number from the original list.  Is there a way to say to remap it's value to QC when it is PQ in the on statement   Here's the complete code   %macro searchpremium /minoperator; Data dest1.found_agreements; attrib /*********************************** Agreement Number location information ***************************************************/ KEY label="Record to be anonymized key" length=$31. format=$char31. informat=$char31. ORGNL_SAS_DS_ROW_NBR label="Original SAS dataset Row number" length=8 format=8. informat=8. agreement_nbr label="Agreement Number" length=$20. format=$char20. informat=$char20. cie label="company cd " length=$2. format=$char2. informat=$char2. lob label="Line Of Business" length=$4. format=$char4. informat=$char4. year label="Year" length=$4. format=$char4. informat=$char4. month label="Month" length=$3. format=$char3. informat=$char3. libname label="path" length=$100. format=$char100. informat=$char100. filename label="Dataset Name" length=$25. format=$char25. informat=$char25. company_cd label="Company CD" length=$1. format=$char1. informat=$char1. policy_expiry_date label="Policy Expiry Date" length=8 format=yymmdd10. informat=yymmdd10. /*********************************** Data Retention dataset information ***************************************************/ /* Modification: appending policy_number and policy_number_src then renaming agreement_nbr and removing duplicate POLICY_NUMBER label="Policy Number" length=$25. format=$char25. informat=$char25. POLICY_NUMBER_SRC label="Policy Number Source" length=$25. format=$char25. informat=$char25. */ AGREEMENT_NBR_DR label="Agreement Number Data Retention" length=$25. format=$char25. informat=$char25. LINE_OF_BUSINESS label="Line of Business" length=$3. format=$char3. informat=$char3. PROVINCE label="Province" length=$10. format=$char10. informat=$char10. LEGACY_UNDERWRITING_COMPANY label="Legacy Underwriting Company" length=$10. format=$char10. informat=$char10. UNDERWRITING_COMPANY label="Underwriting Company" length=$10. format=$char10. informat=$char10. DISTRIBUTOR_NUMBER label="Distributor Number" length=$10. format=$char10. informat=$char10. DISTRIBUTOR_NUMBER_SRC label="Distributor Number Source" length=$10. format=$char10. informat=$char10. SOURCE_SYSTEM label="Source System" length=$15. format=$char15. informat=$char15. DATASOURCE_BACKEND_REFERENCE label="Data Source Backend Reference" length=$10. format=$char10. informat=$char10. LAST_TERM_EFFECTIVE_DATE label="Last Term Effective Date" length=8 format=YYMMDD10. informat=YYMMDD10. LAST_TERM_EXPIRY_DATE label="Last Term Expiry Date" length=8 format=YYMMDD10. informat=YYMMDD10. CONTRACT_UUID label="Contract UUID" length=$36. format=$char36. informat=$char36. ; stop; run; %do h=1 %to %sysfunc(countw(&cielist.)); %if %tslit(%scan(&cielist.,&h)) eq 'je' %then %let value=2; %else %if %tslit(%scan(&cielist.,&h)) eq 'gc' %then %let value=0; %else %let value=1; %put &=value; %do i=1 %to %sysfunc(countw(&loblist.))-&value.; %do k=1 %to %sysfunc(countw(&yearlist.)); %do j=1 %to %sysfunc(countw(&monthlist.)); %let fname=%scan(&cielist.,&h)_%scan(&loblist.,&i)_prm%scan(&monthlist.,&j)%scan(&yearlist.,&k); %let libnm=/dwh_actuariat/sasdata/Data_Retention/sas%scan(&yearlist.,&k)/%scan(&cielist.,&h)/%scan(&subfolder.,&i)/; %let cie=%scan(&cielist.,&h); %let does_it_exist=%sysfunc(fileexist(&libnm.)); %put &=does_it_exist &=libnm.; %if &does_it_exist eq 1 %then %do; %put &=libnm &=fname.; libname src1 spde "&libnm."; %put &libnm.&fname.; %if %sysfunc(exist(src1.&fname.)) eq 1 %then %do; /****************** checking if the variable exist ****************/ %if %varexist(src1.&fname.,province_cd) %then %let varname1=province_cd; %else %if %varexist(src1.&fname.,RISK_PROVINCE_CD) %then %let varname1=RISK_PROVINCE_CD; %else %if %varexist(src1.&fname.,POLICY_PROVINCE_CD) %then %let varname1=POLICY_PROVINCE_CD; proc sql; create table table_info as select distinct strip(agreement_nbr) as agreement_nbr length=20, a.ORGNL_SAS_DS_ROW_NBR, "%scan(&cielist.,&h)" as cie length=2, "%scan(&loblist.,&i)" as lob length=4, "%scan(&yearlist.,&k)" as year length=4, "%scan(&monthlist.,&j)" as month length=3, "&libnm" as libname length=100, "&fname" as filename length=25, a.company_cd, catx('|',"%scan(&cielist.,&h)","%scan(&loblist.,&i)","%scan(&yearlist.,&k)","%scan(&monthlist.,&j)",a.ORGNL_SAS_DS_ROW_NBR) as key length=31, input(a.policy_expiry_dt,YYMMDD10.) as policy_expiry_date length=8, b.* from src1.&fname. as a inner join lookup as b on(a.agreement_nbr=b.agreement_nbr_dr and a.&varname1.=b.province and a.company_cd=b.legacy_underwriting_company); quit; %if &sqlobs ne 0 %then %do; proc append base=dest1.found_agreements data=table_info force; run; %end; proc sql; drop table table_info; quit; %end; %else %do; %put "the file &fname. does not exist"; %end; %end; %end; %end %end; %end; %end; proc sort data=dest1.found_agreements out=dest1.found_agreements_in_premds; by key ; run; data listofdatasetstocleanup (keep= libname filename); set dest1.found_agreements_in_premds; run; proc sort data=listofdatasetstocleanup nodupkey out=dest1.listofdatasetstocleanup; by libname filename; run; %mend searchpremium; %searchpremium; ... View more

Hello, I need to test if &csvfile containt the name of a file or no suc file . Then if there is a csv file then import the data into a sas data set . Otherwise, put there is no csv file to import into a sas dataset.   %macro test; %let csvfile=*.csv: No such file or directory; %let csvfile=Master_list_20241031.csv; %if %sysfunc(find(&csvfile,'No such file')) = 0 %then %do; %put "there is a none empty csv file to import into a sas dataset"; %end; %else %do; %put "There is no csv file to import into a SAS dataset"; %end; %mend test; %test; If I select the first csvfile, I am expecting to get  There is no csv file to import into a SAS dataset and if I use the second value I expect to get there is a none empty csv file to import into a sas dataset It is not working properly What's wrong   ... View more