Can you reword your request, as I do not understand how you are going from your input "have" data to your output "want" data If this helps: What are the rules that you are applying Why does your "want" data have multiple identical rows What have you tried? Provide sample data, code and the log Basically the more information you provide in as simple form as possible the greater the chance you will get a helpful response. Here's an example (not related to this post) /* Create sample "have" data */ data have ; format date1 date. date2 date. ; do i=1 to 10 ; date1 =today()+int(ranuni(1)*30) ; date2 =today()+int(ranuni(2)*30) ; output have ; end ; run ; /* What I tried */ proc sql ; create table want as select intck('day',date1, date2) as c_los from have where c_los< 10 ; ; quit ; run ;   ... View more

Simplify the problem makes it simple to understand:   data have ; format date1 date. date2 date. ; do i=1 to 10 ; date1 =today()+int(ranuni(1)*30) ; date2 =today()+int(ranuni(2)*30) ; output have ; end ; run ; proc sql ; create table want as select intck('day',date1, date2) as c_los from have where c_los< 10 ; ; quit ; run ; When you run that you will get the error you were getting 342 proc sql ; 343 create table want as 344 select intck('day',date1, date2) as c_los 345 from have 346 where c_los< 10 ; ERROR: The following columns were not found in the contributing tables: c_los. 347 ; 348 quit ; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 349 run ; ... View more

This looks like a PROC SQL code error not a macro issue   Remove the PROC SQL code from the macro Remove all macro references from the PROC SQL code Run the PROC SQL code Figure out why you are getting  ERROR: The following columns were not found in the contributing tables: c_los. I'm guessing your input data (ds2INP) is missing columns (inptadmitdt and/or inptdischargedt) ... View more

When you say Base SAS what I assume you are talking about is the SAS Windowing Environment Which is different from other interfaces e.g. SAS Enterprise Guide, SAS Studio It comes down to personal preference which is better. Like @PaigeMiller I started using SAS before SAS Enterprise Guide or SAS Studio existed and prefer the windowing environment, for the reasons Paige mentioned.  ... View more

Hi @slee77  I think this is what you are looking for. I simplified your original data and code. Thanks   data work.have ; infile datalines ; input data_date :yymmdd8. /* YYMMDD8. */ seccode :$4. ME_October_t :32. ; put _all_ ; format data_date date7. ; datalines; 20200101 0001 1 20200102 0001 2 20200131 0001 3 20200201 0001 4 20200202 0001 5 20200229 0001 6 20200101 0002 7 20200102 0002 8 20200131 0002 9 20200201 0002 10 20200202 0002 11 20200229 0002 12 20200701 0001 13 20200702 0001 14 20200731 0001 15 20200801 0001 16 20200802 0001 17 20200831 0001 18 20200701 0002 19 20200702 0002 20 20200731 0002 21 20200801 0002 22 20200802 0002 23 20200831 0002 24 ; proc sort data =work.have out =work.srtdHave ; by seccode data_date ; run ; /* Create a look up dataset containing just end of month data for each secode (work.lookup) Add matchDate variable to the master data (work.master). This variable will be used to pull in the me6 value. */ data /* Define the work.lookup SAS Dataset */ work.lookup( rename=( ME_October_t=me6 ) keep= seccode ME_October_t matchDate ) work.master ; format matchdate date7. ; set work.srtdhave ; /* If it's the last day of a month then output that record to the lookup table */ if day(data_date+1)=1 then do ; /* is the current observation the last in a month? */ matchDate=intnx("month",data_date,0) ; /* Set the matchDate to the start of the month */ output work.lookup ; end ; /* Add a matchDate to the master dataset */ matchdate=intnx('month',data_date,-6) ; output work.master ; run ; /* Merge the datasets bu seccode and matchdate if there is a record in the master dataset */ data work.want ; merge work.master (in=master) work.lookup ; by seccode matchdate ; if master ; run ; /**/ ... View more

This code does something similar although it outputs to the SAS log  options nomprint nomprintnest nomlogic nomlogicnest nosymbolgen nosource nosource2; /* Parse Registry File */ /* v0.1 */ /* Andy.Mayes@SAS.COM */ /* Feb 2021 */ /* Updates */ /* 17FEB2021 deculp Traverse directory and parse each registry file in the directory */ /* 18Feb2021 snzroo Merged my changes to Dereks and formatted output example: ******************************************************************************* Filename 1: Meta-DeploymentRegistry.txt ******************************************************************************* =============== HOT FIX SUMMARY Meta-DeploymentRegistry.txt =============== SAS Thread-safe Compiler and Symbolic Differentiator 9.57 SAS Model Implementation Platform Server Configuration 3.2 SAS High-Performance Risk Server Component 4.2 F7P004 =============== ******************************************************************************* Filename 2: Mid-DeploymentRegistry.txt ******************************************************************************* =============== HOT FIX SUMMARY Mid-DeploymentRegistry.txt =============== SAS Infrastructure for Risk Management Mid-Tier 3.6 SAS Thread-safe Compiler and Symbolic Differentiator 9.57 SAS Risk and Finance Workbench Mid-Tier 3.2 D4N006 =============== */ /* 22Feb2021 snzroo Small changes to improve output, and restrict to files with a specific externsion */ /* 04Mar2021 snzroo RegistryParser_rev04.sas Added file match set fileMatch macro variable to PERL Regular Expression Only processes files that match the expression This allows code to run against directory with multiple files and only report on required registry reports e.g. FileMatch=DeploymentRegistry\.txt Will only analyse files that have DeploymentRegistry.txt in their file name EXT macro no longer required */ /* 20Apr2021 snzroo RegistryParser_rev05.sas Fixed warning message */ /* 19Jul2022 snzroo RegistryParser_rev06.sas Removed the need to define the ext macro variable Now code pulls the file extenstion (ext) from the fileMatch */ /* Instructions */ /* 1. Set VRDIR to the directory that contains the ViewRegistry Reports */ /* 2. Set EXPR to the SAS components to find and report using a PERL Regular Expression */ /* 3. DELETE - Set EXT to the file extension you want to interrogate */ /* 3. Set fileMatch to a regular expression to find matching files */ /* 4. Submit program, and view results in the SAS log */ %let vrdir = <Change this> ; /* SNZROO 2022-07-19 Start */ /* Remove the need to define the ext macro */ /* %let ext = txt; /**/ /* SNZROO 2022-07-19 End */ /* fileMatch = DeploymentRegistry.*\.txt Searches for any file "DeploymentRegistry<other characters>.txt" */ %let fileMatch = .*DeploymentRegistry.*\.txt* ; /* %let expr = modimp|modimpsvr|modimpmiddoc|modimpmid|modimpsvrc|modimpsvr; /**/ %let rfw = stresssvr|stressmid|ssrsvr|ssrsvrc|stressmid; /* RFW */ %let irm = rmifirmmvac|riskminmid|rmifirmmva; /* IRM 3.6 */ %let cmp = cmp|tkcmp ; /* SAS (Thread-safe) Compiler and Symbolic Differentiator */ %let hpr = hprisksvr|riskcmnmid|hpriskmid|risk|rskwkgpmid ; /* HPRISK */ %let mip = modimpmid|modimpsvr|riskmodedmid ; /* MIP */ %let rgf = rskmgtsvr|rgpmid|rgpadm ; %let expr = base|hprisksvr|risk|hpriskmid|modimp|cmp|tkcmp|stressmid|stresssvrc|stresssvr|modimpsvr|modimpmiddoc|modimpmid|modimpsvrc|modimpsvr; /**/ %let expr = &hpr|&mip ; * options mlogic mprint symbolgen ; %macro list_hot_fixes(dir,fname,expr); filename regfile "&dir.\&fname."; data readReg; file="&fname" ; reProduct = prxparse("/Product: (&expr.)/"); reDisplayName = prxparse("/Display Name:/") ; deDisplayName = prxparse("s/Display Name://") ; reVersion = prxparse("/Version:/") ; deVersion = prxparse("s/(Version:)|(Display Version:)//") ; *deVersion = prxparse("s/Version://") ; reHotFixEntry = prxparse("/Hot Fix Entry: Hotfix/") ; deHotFixEntry = prxparse("s/Hot Fix Entry: Hotfix//") ; infile regfile; input; /* Scan for Product */ *if findw(_infile_,"risk") then do ; if prxmatch(reProduct,_infile_) then do; * put "PRODUCT : " _infile_; /* Repeat until end of Product */ hotFixFound=0; do until(findw(_infile_,"-----")); input; * put _infile_; if prxmatch(reDisplayName,_infile_) then do ; displayname=prxchange(deDisplayName,1,_infile_) ; * put "Display Name: " _infile_ ; end ; else if prxmatch(reVersion,_infile_) then do ; version=prxchange(deVersion,1,_infile_) ; * put "VERSION : " _infile_ ; end ; else if prxmatch(reHotFixEntry,_infile_) then do ; * put "HOT FIX : " _infile_ ; hotFixFound=1 ; hotFix=prxchange(deHotFixEntry,1,_infile_) ; output ; end ; end ; if hotFixFound=0 then output ; end ; run ; /* Pull version */ /* Pull Hot Fixes */ proc sort data=readreg out=srtdReg ; by displayname ; run ; data _null_ ; set srtdReg; by displayname ; if last.displayname then put displayname @75 version @85 hotfix ; run ; %mend list_hot_fixes; %macro get_viewregistry_files(fr,dir,type); %global nfiles; proc sql noprint; /* Get number of files */ select count(fname) into : nfiles from work.vrfiles ; %let nfiles=%trim(%left(&nfiles)); /* Assign each filename to a macro variable */ %do zz=1 %to &nfiles; %global fname&zz; %global ext&zz; %end; select fname into : fname1 - : fname%trim(&nfiles) from work.vrfiles ; select ext into : ext1 - : ext%trim(&nfiles) from work.vrfiles ; quit; %do i=1 %to &nfiles ; %put DEBUG - &&fname&i ; %end ; %mend get_viewregistry_files; %macro list_hfs_by_vrfile(fileref,type); /* For each ViewRegistry Report, list the release details for the relevant components */ %get_viewregistry_files(&fileref,&vrdir,&type); options nosymbolgen nonotes nomprint nomprintnest nomlogic nomlogicnest nosource nosource2; /* dm 'clear log'; SNZROO 04/07/21 commenting this out, as I want to see the full log */ %put ; %put ; %put List of files in: &vrdir; %put ; %put With ViewRegistry details where:; %put File extension is: &type; %put And hot fix component is in: &expr; %put ; %put ; %do ii = 1 %to &nfiles; %put ; %put ; %put *******************************************************************************; %put Filename &&ii: &&fname&ii; %put *******************************************************************************; %put ; /* %if %UPCASE(&&ext&ii)=%UPCASE(&type) %then %do; */ %list_hot_fixes(&vrdir,&&fname&ii,&expr); /* %end; */ %end; %mend list_hfs_by_vrfile; /* Store each filename in the ViewRegistry directory in a SAS data set */ filename v "&vrdir"; data work.vrfiles (keep=fname ext); reFileMatch = prxparse("/&FileMatch./"); did=dopen("v"); filecount=dnum(did); do i=1 to filecount; fname=dread(did,i); put fname= ; ext=trim(scan(fname,-1,".","r")); lext=length(ext); lfil=length(fname); /* SNZROO 2022-07-19 Start */ /* Remove the need to define the ext macro */ call symput("ext",ext) ; /* SNZROO 2022-07-19 End */ /*file=substr(fname,1,lfil-lext-1);*/ /*if ext="&ext" then */ if prxmatch(reFileMatch,fname) then output; end; rc=dclose(did); run; %list_hfs_by_vrfile(v,&ext); ... View more

Find below a simple example using the Excel date Values you had in the initial post A couple of things to note: Excel stores dates as a number e.g. 1 = 01 Jan 1990, 2 = 02 Jan 1990, ... Excel doesn't appear to handle dates prior to 1900 (I'm no Excel expert, just an observation when looking into this post) SAS stores dates as the number of days since 01 Jan 1960 e.g. 0 = 01 Jan 1960, 1 = 02 Jan 1960, ... Once you know this it's a relatively simple task to convert Excel date values to SAS date values, you just need to know the number of days to subtract from the Excel date value to convert it to a SAS Date Value. Knowing that 01 Jan 1960 is represented as 21,916 in Excel and 0 in SAS tells us to subtract 21,916 from the Excel Date Value. /* Simulated date values from Excel */ data have ; infile cards ; input excelDateValue ; cards ; 44582 43526 40330 21916 1 ; /* Read the simulated date values and conver to SAS Date values */ data want ; format sasDateValue date7. ; set have ; sasDateValue=excelDateValue-21916 ; put sasDateValue= 8. sasDateValue= ; run ;   ... View more

I'd suggest converting the numeric value into a SAS date value SAS Date, Time, and Datetime Values  Then you can use SAS date formats  Better still would be to track back where adjudication_dt_id is created and create/read it as a SAS Date value to start with  Here's an example: data _null_ ; file "test.txt" ; infile cards ; input ; put _infile_ ; cards ; 20230123 20221205 20220401 ; data readText ; infile "test.txt" ; input myDate yymmdd8. ; put "no format : " myDate ; put "date7. : " myDate date7. ; put "mmyy. : " myDate mmyy. ; put "yyq. : " myDate yyq. ; put "===========================" ; run ;   ... View more

@KarimaTouati  We are really going to see your data and code, a lot of people (including me) will not download attachments for obvious reasons.   One thing I do not understand is how your raw data is CSV format. If it is then how would you know if the following is 3 values (123, 12 and example, ie 2 numbers and a text string) or just 2 (123.12 and example ie 1 number and a text string)? 123,12,example Even if you have column headers (see below) you would still have issues Var1, Var2 123,45,67 Would this be 123 and 45.67, or 123.45 and 67 It would be helpful if you could create some SAS code that replicates your raw data file e.g. data _null_ ; file "myTemp.csv" ; do i=1 to 10 ; x=ranuni(1)*10 ; y=ranuni(2)*10 ; put x commax5.2 "," y commax5.2 ; end ; run ; ... View more

Is this what you want to do? data want ; do i=1 to 5 ; a=i ; b=i ; c=i ; output want ; end ; run ; If not then I suggest you provide a clear example of the dataset you want to create  ... View more

Take a look at PROC TRANSPOSE    Transpose Example  ... View more

The FREQ Procedure Documentation  ... View more