Thank heaps for the code and explanation. I am now able to import the data in SAS successfully. Regards, Sara ... View more

Hello Kurt,   Thanks for the macro; it worked for my CSV file.   Much appreciated.   Regards, Sara ... View more

Thanks, Tom - you are correct, it was a typo this is about CSV.  ... View more

here is the link to access my sample CSV file https://drive.google.com/file/d/1T_Xo9bVHDuRq0lUNtVUwxwD6juRfJitX/view?usp=sharing Regards, Sara ... View more

Hello Tom,   Thanks for the code.   I use the code and can see data in SAS now but somehow the date column is blank.  Please can you check my excel file via below link and advise.  https://drive.google.com/file/d/1T_Xo9bVHDuRq0lUNtVUwxwD6juRfJitX/view?usp=sharing   Regards, Sara data want; length id $20 col date value 8 ; format date DDMMYY10.; infile "C:\Users\Return_Daily.csv" DLM=',' DSD truncover lrecl=1000000 ; array dates [1000] _temporary_; input id @; if _n_=1 then do; do ncol=1 by 1 until(missing(date)); input date :DDMMYY10. @ ; /*when I use date9. WORK.WANT has 0 observations and 5 variables*/ end; ncol=ncol-1; retain ncol; end; else do col=1 to ncol; input value @; output; end; run; NOTE: 4473 records were read from the infile "C:\Users\Return_Daily.csv". The minimum record length was 8141. The maximum record length was 87038. NOTE: The data set WORK.WANT has 36361832 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 57.30 seconds cpu time 41.76 seconds @Tom wrote: DATES do not make good variable names.  DATE values are NUMERIC.  Variable names are CHARACTER. You probably will want to write your own data step to read that file.  Then you can read the dates into variables instead.   So let's assume you have one ID variable and then an unknown number of numeric variables with DATE values as the column header.  So read the dates from the first row and then attach them to the values read from the other rows.  For example something like this data want; length id $20 col date value 8 ; format date yymmdd10.; infile "C:\Users\Return_Daily.csv" dsd truncover lrecl=1000000 ; array dates [1000] _temporary_; input id @; if _n_=1 then do; do ncol=1 by 1 until(missing(date)); input date :date9. @ ; end; ncol=ncol-1; retain ncol; end; else do col=1 to ncol; input value @; output; end; run;   ... View more

@Kurt_Bremser  Thanks, heaps for your help, I really appreciate that.   I will follow the protocol next time.   Regards, Sara  ... View more

Thanks for your prompt response. The ID 1 - last four missing values in age_desired is due to the death_date, as the person is deceased so there is no further age calculation. I tried the code you provided and can see the matched output (age-column) but I want blank cells when the person Death_Date is less than the Date column. ID Birth_Date Death_Date Date age_desired age 1 30/08/1996 20/09/2019 31/01/2019 269 269 1 30/08/1996 20/09/2019 28/02/2019 270 270 1 30/08/1996 20/09/2019 29/03/2019 271 271 1 30/08/1996 20/09/2019 30/04/2019 272 272 1 30/08/1996 20/09/2019 31/05/2019 273 273 1 30/08/1996 20/09/2019 28/06/2019 274 274 1 30/08/1996 20/09/2019 31/07/2019 275 275 1 30/08/1996 20/09/2019 30/08/2019 276 276 1 30/08/1996 20/09/2019 30/09/2019 277 277 1 30/08/1996 20/09/2019 31/10/2019 . 277 1 30/08/1996 20/09/2019 30/11/2019 . 277 1 30/08/1996 20/09/2019 30/12/2019 . 277 1 30/08/1996 20/09/2019 31/01/2020 . 277 2 28/02/1997 . 31/01/2008 131 131 2 28/02/1997 . 29/02/2008 132 132 2 28/02/1997 . 31/03/2008 133 133 2 28/02/1997 . 30/04/2008 134 134 2 28/02/1997 . 30/05/2008 135 135 2 28/02/1997 . 30/06/2008 136 136 2 28/02/1997 . 31/07/2008 137 137 2 28/02/1997 . 29/08/2008 138 138 3 1/10/2007 . 30/10/2020 156 156 3 1/10/2007 . 30/11/2020 157 157 3 1/10/2007 . 31/12/2020 158 158 3 1/10/2007 . 29/01/2021 159 159 3 1/10/2007 . 26/02/2021 160 160 3 1/10/2007 . 31/03/2021 161 161 3 1/10/2007 . 30/04/2008 6 6 3 1/10/2007 . 30/05/2008 7 7     ... View more

ID Birth_Date Death_Date Date age_desired age age2 1 30/08/1996 20/09/2019 31/01/2019 269 276 277 1 30/08/1996 20/09/2019 28/02/2019 270 276 277 1 30/08/1996 20/09/2019 29/03/2019 271 276 277 1 30/08/1996 20/09/2019 30/04/2019 272 276 277 1 30/08/1996 20/09/2019 31/05/2019 273 276 277 1 30/08/1996 20/09/2019 28/06/2019 274 276 277 1 30/08/1996 20/09/2019 31/07/2019 275 276 277 1 30/08/1996 20/09/2019 30/08/2019 276 276 277 1 30/08/1996 20/09/2019 30/09/2019 277 276 277 1 30/08/1996 20/09/2019 31/10/2019 . 276 277 1 30/08/1996 20/09/2019 30/11/2019 . 276 277 1 30/08/1996 20/09/2019 30/12/2019 . 276 277 1 30/08/1996 20/09/2019 31/01/2020 . 276 277 2 28/02/1997 . 31/01/2008 131 131 131 2 28/02/1997 . 29/02/2008 132 132 132 2 28/02/1997 . 31/03/2008 133 133 133 2 28/02/1997 . 30/04/2008 134 134 134 2 28/02/1997 . 30/05/2008 135 135 135 2 28/02/1997 . 30/06/2008 136 136 136 2 28/02/1997 . 31/07/2008 137 137 137 2 28/02/1997 . 29/08/2008 138 138 138 3 1/10/2007 . 30/10/2020 156 156 156 3 1/10/2007 . 30/11/2020 157 157 157 3 1/10/2007 . 31/12/2020 158 158 158 3 1/10/2007 . 29/01/2021 159 159 159 3 1/10/2007 . 26/02/2021 160 160 160 3 1/10/2007 . 31/03/2021 161 161 161 3 1/10/2007 . 30/04/2008 6 6 6 3 1/10/2007 . 30/05/2008 7 7 7   /**WITH "C"**/ DATA WANT1; SET WANT; age = floor(intck('month',birth_date,ifn(death_date = .,date,death_date),"C")); RUN; /**WITHOUT "C"**/ DATA WANT2; SET WANT1; age2 = floor(intck('month',birth_date,ifn(death_date = .,date,death_date))); RUN; @KurtBremser Thanks, I tried both of the codes and it worked when the death_date is missing. Please see the output desired age. I want age based on each month as appeared in the Date column.   Regards, Sara   ... View more

THANKS HEAPS. ... View more

Thanks, everyone for your valuable comments and suggestions. it seems I have to learn a lot.   Regards, Sara  ... View more

Hi Tom,   1. yes the CSV files are of the same structure with the same variables in the same order. 2. I know the names of the CSV files but that name doesn't matter so I import them as D&i 3. I want to use all imported files to do the analysis.  so if I want to format the dates of all the imported files - how do I write the code to call the D&i here even though D&i are not in sequence? %macro test1; %do i =1 %to 100; data test_D&i (rename = ( var2 = debt var3 = datestamp)) ; set D&i; IF var3 = 0 OR var3 = "0" THEN DELETE; YEAR1 = SCAN (var3,3 ,'-') * 1; MONTH1 = SCAN (var3,2,'-'); DAY1= SCAN (var3,1,'-')* 1; DAY = DAY(var3); MONTH = MONTH(var3); YEAR = YEAR(var3); date = mdy(month,day, Year); format date date9.; drop day1 month1 year1; run; %end; %mend ; %test1;   or do I need to make the changes in my import macro step thanks?  proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=d&cnt dbms=csv replace;           ... View more