topic Re: Reading multiple records into one observation in New SAS User

Reading multiple records into one observation

blowsnow__ — Fri, 02 Nov 2018 17:47:03 GMT

Greetings all,

I am trying to read the following data set into one observation for each sales associate. First of all, how can I ignore the first meaningless line? And suppose I only want the output to show three variables - Name, Latest Sales Date, and Total Sales. For the dates column, how can I create the variable "Latest Sales Date" that only indicates the most recent date from each salesperson? In addition, how can I create the other variable "Total Sales" that sums up the total sales amount of each person?

1234567890123456789012345678901234567890

David Wong 1/Aug/2018 13200

Brian Leung 15/Sep/2018 23450

10/Sep/2018 33000

Mary Chan 3/Jul/2018 45600

20/Aug/2018 37800

1/Sep/2018 21500

30/Aug/2018 42000

John Tam 12/Sep/2018 35000

Re: Reading multiple records into one observation

PeterClemmensen — Fri, 02 Nov 2018 17:50:05 GMT

How is this data stored? Already in a sas data set or a txt/Excel file?

Re: Reading multiple records into one observation

blowsnow__ — Fri, 02 Nov 2018 17:53:01 GMT

This data is stored as a .txt file

Re: Reading multiple records into one observation

PeterClemmensen — Fri, 02 Nov 2018 18:05:16 GMT

ok. First, read your data into a SAS data set like this. Simply inset the relevant path instead of path

data have;
   length name $15;
   infile "path.txt" firstobs=2;
   input name $ 1-15 date:date11. sales;
   format date date11.;
run;

Re: Reading multiple records into one observation

Tom — Fri, 02 Nov 2018 18:06:33 GMT

You just need to retain the name.

data want ;
  infile 'myfile.txt' truncover ;
  length Dummy Name $15 Latest_Sales_Date Total_Sales 8;
  input dummy $15.  Latest_Sales_Date date11. Total_Sales ;
  format Latest_Sales_Date date9.;
  retain name;
  name=coalescec(dummy,name);
  drop dummy;
run;

Re: Reading multiple records into one observation

blowsnow__ — Sat, 03 Nov 2018 13:55:48 GMT

Well the output shows several observations for each person. What if I only want one observation for each salesperson, with their most recent sales date and sum of total sales instead?

Re: Reading multiple records into one observation

Tom — Sat, 03 Nov 2018 15:46:33 GMT

Use PROC SUMMARY.

@blowsnow__ wrote:

Well the output shows several observations for each person. What if I only want one observation for each salesperson, with their most recent sales date and sum of total sales instead?