BookmarkSubscribeRSS Feed
CMANOJ622
Calcite | Level 5

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;
2 REPLIES 2
Tom
Super User Tom
Super User

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
Super User Tom
Super User

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:

Tom_0-1737990752678.png

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

Tom_1-1737990889756.png

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;

Tom_0-1737995157691.png

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 518 views
  • 0 likes
  • 2 in conversation