Reading the CSV data and doing the sum function based on filter automaticllay

[CMANOJ622]

Hi All.

I have attached dummy data.  I need to import do the sum function for Date's column.

The problem here is each time when I get the data the dates will change. I need SAS code which import data and do the sum function as and when the date change. Currently I am doing it manually using infile,informat ,format and Input code to get the data in to SAS then apply sum function on this. When I get the new data I manually change the dates in informat ,format and Input code  and Sum code

Pleas find the code below.. I need to automate the manual part.

%Macro Import(xxxx);

data &xxxx.;

infile "Folder Path"

   delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

    informat COMBO $41. ;

    informat PL $50. ;

Manual Copying each time if the dates are new

---------------------------------------------

    Informat "2024-01-01"N best32.;

Informat "2024-02-01"N best32.;

Informat "2024-03-01"N best32.;

Informat "2024-04-01"N best32.;

Informat "2024-05-01"N best32.;

Informat "2024-06-01"N best32.;

    format COMBO $41. ;

    format PL $50. ;

Manual Copying each time if the dates are new

---------------------------------------------

    format "2024-02-01"N best12.;

format "2024-03-01"N best12.;

format "2024-04-01"N best12.;

format "2024-05-01"N best12.;

   input

            COMBO  $

            PL  $

Manual Copying each time if the dates are new

---------------------------------------------

        "2024-01-01"N

"2024-02-01"N

"2024-03-01"N

"2024-04-01"N

"2024-05-01"N

2024-06-01"N

"2024-07-01"N

;

run;

proc sql;

Create table xxxxx_xxxx_&xxxx AS

select * from &xxx.

where upcase(PL) LIKE'X2';

quit;

proc sql;

Create table xxx_Summary_&xxx AS

select A,B,C,D,

Manual Copying each time if the dates are new

---------------------------------------------

Sum("2024-01-01"N) as "Jan-2024"N,

Sum("2024-02-01"N) as  "Feb-2024"N,Sum("2024-03-01"N) as  "Mar-2024"N,

Sum("2024-04-01"N) as  "Apr-2024"N,Sum("2024-05-01"N) as  "May-2024"N,

Sum("2024-06-01"N) as  "Jun-2024"N,Sum("2024-07-01"N) as  "Jul-2024"N,

from xxxxx_xxxx_&xxxx 

Group by A,B, C,D;

quit;

[Tom]

Why did you attach an XLSX file if you are reading from a CSV file?

Warning: If you let EXCEL automatically open a CSV file it will frequently change some of the values.  Like converting ID values with hyphens , like 5-12 , into DATE values.

Why do you attach BEST (the name of a SAS supplied FORMAT) as in INFORMAT to the variables with names that look like date strings?  If you ask SAS to use an INFORMAT named BEST it will treat it as an ALIAS for the normal numeric informat.  So why bother attaching any informat to those variables?

[Tom]

If you have a delimited text file (like a CSV file) with that structure of two real variables and then column headers that are DATE values you can read it directly into a transposed structure.

Say you have this CSV file today.

combo,pl,2023-01-01,2023-02-01,2023-03-01
A,B,1,2,3
A,C,4,5,6

But tomorrow you will get a new one that has another column.

Write a program that automatically adjusts for the number of "date" columns.

data tall;
  infile 'myfile.csv' dsd truncover lrecl=1000000 length=ll column=cc;
  length combo $7 pl $5 date 8 value 8;
  array dates[500] _temporary_;
  if _n_=1 then do;
    input combo pl @ ;
    do ndates=1 by 1 until(cc>ll);
        input dates[ndates] :yymmdd. @;
    end;
    input;
  end;
  input combo pl @;
  do index=1 to ndates;
     date = dates[index];
     input value @;
     output;
  end;
  retain ndates;
  drop ndates index;
  format date yymmdd10.;
run;

So you get a dataset like this:

Which can easily be SUMmed using basic tools like PROC SUMMARY.

proc summary nway data=tall;
  class date ;
  var value;
  output out=want sum= ;
run;

proc print;
run;

Result

Or perhaps like this:

proc summary data=tall ;
  class combo pl ;
  types combo pl ;
  output out=want sum(value)= min(date)=start max(date)=end;
run;