topic Re: How to output the last part (data programming) in SAS Programming

How to output the last part (data programming)

kennyA — Sat, 03 Nov 2018 15:03:40 GMT

Hello,

I am new to SAS and I need some help here 🙂

Below is the question:

Write a SAS program that reads each record of SalesRecords.txt into a SAS data set named Sales_Total. Sales_Total must be in the following form:

It contains only one observation for each salesperson.
Each observation contains only the following variables in this order: the salesperson’s name, the salesperson’s latest sales date, the number of days between the salesperson’s latest sales date and the 30th of September 2018, the salesperson’s total sales, and the running total of sales for all salespersons.

You are allowed to include only one DATA step in your SAS program. Keep in mind that some salespersons in the raw data file may have only 1 record, and the records of the same salespersons are not sorted in the order of date. The first few observations of Sales_Total are displayed below:

The labels shown in the above Sales_Total are for reference only. You don’t need to define them in your SAS DATA step program. You may name the variables in any way you want as long as they are valid SAS variable names."

So far I am only able to do the following. I do not know how to output the last part (last photo), such as combining the total sales and the latest sales date, etc..

data Sales_Total;

infile 'c:\temp\SalesRecords.txt’;

input salesname & $1-15 salesdate date11. 16-26 salesamt 28-32;

Thanks a lot 🙂

Re: How to output the last part (data programming)

Kurt_Bremser — Sat, 03 Nov 2018 15:15:38 GMT

Since this is the third post with the same identical question, use the search function here.

And it should return the one question right before yours: https://communities.sas.com/t5/SAS-Programming/Creating-variables-from-existing-variables/m-p/510163

Re: How to output the last part (data programming)

VDD — Sat, 03 Nov 2018 15:41:38 GMT

chasing managers bits and pieces leads to confusion. Please ask everything in one post. Time wasted is never retrievable but data is.

Re: How to output the last part (data programming)

kennyA — Sat, 03 Nov 2018 15:43:36 GMT

sorry 😞
I didnt notice it but I think my post is clearer in terms of the requirements

Re: How to output the last part (data programming)

novinosrin — Sat, 03 Nov 2018 17:38:12 GMT

dm log 'clear';
data _null_;
infile datalines firstobs=2 eof=l ;
input name $ 1-15 lt_sales_date :date11. person_total_sales 28-32;
if _n_=1 then do;
  dcl hash H () ;
   h.definekey  ("name") ;
   h.definedata ("name","latest_sales_date","daysdiff", "salep_tot","running_tot") ;
   h.definedone () ;
end;
retain _n latest_sales_date  running_tot ;
if not missing(name) then _n=name;
else name=_n;
if lag(name) ne name then do;
salep_tot=person_total_sales;
latest_sales_date=lt_sales_date;
end;
else do;
salep_tot+person_total_sales;
latest_sales_date=max(lt_sales_date,latest_sales_date);
end;
daysdiff='30sep2018'd-latest_sales_date;
running_tot+person_total_sales;
h.replace();
return;
l:
h.output(dataset:'want');
datalines;
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
;
run;

Re: How to output the last part (data programming)

novinosrin — Sat, 03 Nov 2018 17:43:35 GMT

@kennyA Are you and the other thread https://communities.sas.com/t5/SAS-Programming/Creating-variables-from-existing-variables/m-p/510174#M137281 author blowsnow__ the same or mates by any chance?. Of course, not my business or concern,but the questions are so identical, so was curious.

Anyways, if it's hw, I wouldn't class that as easy or even medium for new users if they are just average like me coz unless one knows about techniques like look above/look ahead the stated condition-"You are allowed to include only one DATA step in your SAS program" poses a significant challenge for new to intermediate users in my humble opinion

Re: How to output the last part (data programming)

blowsnow__ — Sat, 03 Nov 2018 18:12:07 GMT

Thanks for the suggestion! And to answer your curiosity, the author and I should be mates if I'm not mistaken.

Anyway, for the output table, is there any methods to rearrange the order so that it looks the same as shown in the requirements above?

Re: How to output the last part (data programming)

VDD — Sat, 03 Nov 2018 18:17:40 GMT

@novinosrin hats off to you.

I was having an issue with calculating the days and keep getting over the expected number of days in my output.

Your code is great and if you don't mind I would like to keep it and use for other to learn from in future demonstrations.

Re: How to output the last part (data programming)

novinosrin — Sat, 03 Nov 2018 18:18:37 GMT

@blowsnow__ Good mates eh? LOL

here you go:

dm log 'clear';
data _null_;
infile datalines firstobs=2 eof=l ;
input name $ 1-15 lt_sales_date :date11. person_total_sales 28-32;
if _n_=1 then do;
  dcl hash H (ordered:'y') ;
   h.definekey  ("n","name") ;
   h.definedata ("name","latest_sales_date","daysdiff", "salep_tot","running_tot") ;
   h.definedone () ;
end;
retain _n latest_sales_date  running_tot ;
if not missing(name) then _n=name;
else name=_n;
if lag(name) ne name then do;
salep_tot=person_total_sales;
latest_sales_date=lt_sales_date;
n+1;
end;
else do;
salep_tot+person_total_sales;
latest_sales_date=max(lt_sales_date,latest_sales_date);
end;
daysdiff='30sep2018'd-latest_sales_date;
running_tot+person_total_sales;
h.replace();
return;
l:
h.output(dataset:'want');
datalines;
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
;
run;

Re: How to output the last part (data programming)

novinosrin — Sat, 03 Nov 2018 18:25:12 GMT

"Your code is great and if you don't mind I would like to keep it and use for other to learn from in future demonstrations"

@VDD to your "if you don't mind" hahahahahaha You made my weekend. I still am hung over from last night. I will have few more tonight .lol

Come on!, none of us own any of the codes once it is posted online in a forum like this. If we start requesting like this, how many times- "if you don't mind" should i iterate to people like John King,Mark Keintz, Art T, PGstats, PD, Ksharp, Reeza , Freelancereinhard etc:)

Re: How to output the last part (data programming)

VDD — Sat, 03 Nov 2018 18:26:25 GMT

understood. Have one on me and I will pay for it in the next life if we get one and if not then at least you had another reason to have one. Bottoms

Re: How to output the last part (data programming)

novinosrin — Sat, 03 Nov 2018 18:28:37 GMT

Just to thank you on a "healthy" note, I was really dull, weak and tired but that message of yours charged me up truly.

Re: How to output the last part (data programming)

Reeza — Sun, 04 Nov 2018 01:32:04 GMT

Just an FYI, If your course has not covered hash and you submit a hash solution....

Re: How to output the last part (data programming)

mkeintz — Sun, 04 Nov 2018 05:00:39 GMT

Although @novinosrin's hash object solution is neat, you can solve this problem just by wallowing around in basic early SAS, utilizing some of the power of the INPUT statement, especially the trailing @, trailing @@, and the record advance character '/':

data want (drop=_:);
  infile 'c:\temp\salesrecords.txt' end=end_of_data;
  input @1 name $char13. @ ;

  do until (_nxt_name^=' ');
    input _date :date11. _sales @;
    latest_sales_date=max(latest_sales_date,_date);
    ndays='30sep2018'd-latest_sales_date;
    personal_total=sum(personal_total,_sales);
    running_total+_sales;
    if end_of_data=0 then input / @1 _nxt_name $char13. @@;
    else _nxt_name='ZZZ';
  end;
  format latest_sales_date date9.;
run;

Trailing @ means hold the pointer, such that the next INPUT statement starts where the pointer is held. But don't hold the pointer across data step iterations (of which there are 4 - one per sales person).
Within each data step iteration (i.e. each sales person), loop through records until the name in the following record is not blank. If the current record is the end of file, then synthetically assign a non-blank so the next name.
"personal_total=sum(personal_total,sales) will
1. treat a missing value for personal_total as a zero, and
2. personal_total is initialized to missing when the next data step iteration begins.
running_total+sales; keeps a running total and never resets it to missing.
The "if end_of_data=0" statement
1. tells sas to advance one record to read the next name (to see whether it is blank).
2. The trailing "@@" says to hold the pointer tightly. I.e. even when the data step goes to the next iteration (next sales person) the record is not thrown away, and therefore will supply the next actual name with the input statement.

Re: How to output the last part (data programming)

blowsnow__ — Sun, 04 Nov 2018 08:53:14 GMT

Much appreciated for the thorough explanation! But when I try to run the program, however, the output is as follows:

How can I get rid of the first line from the data set and replace it with the person "David Wong" instead?

Re: How to output the last part (data programming)

blowsnow__ — Sun, 04 Nov 2018 08:56:03 GMT

Never mind, I added firstob=2 in my infile statement and it works. Thanks again for your detail explanation!