I have tens of thousands records shown in the below format. Example: one aggregate amount ($3200) for multiple invoice numbers (32, 33, 34, 35). Here is how I created by using Proc SQL:
PROC SQL;
CREATE TABLE INVENTORY AS
SELECT
DATE,
AMOUNT,
INVOICE
FROM DATA.DATA;
WHERE DATE = '31OCT2016'D;
QUIT;
Date | Amount | Invoice # |
10/31/2016 | 3200 | 32, 33, 34, 35 |
Now the question is:
Is there any way that I could put the invoice # into different rows while keeping the aggregate amount of $3200 in the first row only? Like Below:
Date | Amount | Invoice # |
10/31/2016 | 3200 | 32 |
10/31/2016 | 0 | 33 |
10/31/2016 | 0 | 34 |
10/31/2016 | 0 | 35 |
Thanks
This is easy in a data step.
You want to put an output statement into a loop where invoice=32, then 33, then 34, then 35. But after the first output, you want to set amount to a missing value. So the real issue is how to make a loop over the character variable INVOICELIST (="32,33,34,35"). This program does that:
Notes:
data have;
attrib date format=mmddyys10.
amount length=8
invoicelist length=$20;
input date mmddyy10. amount invoicelist;
datalines;
10/31/2016 3200 32,33,34,35
run;
data want;
set have;
do I=1 to countw(invoicelist,',');
invoice=input(scan(invoicelist,I,','),4.);
output;
amount=.;
end;
drop invoicelist;
run;
This is easy in a data step.
You want to put an output statement into a loop where invoice=32, then 33, then 34, then 35. But after the first output, you want to set amount to a missing value. So the real issue is how to make a loop over the character variable INVOICELIST (="32,33,34,35"). This program does that:
Notes:
data have;
attrib date format=mmddyys10.
amount length=8
invoicelist length=$20;
input date mmddyy10. amount invoicelist;
datalines;
10/31/2016 3200 32,33,34,35
run;
data want;
set have;
do I=1 to countw(invoicelist,',');
invoice=input(scan(invoicelist,I,','),4.);
output;
amount=.;
end;
drop invoicelist;
run;
You don't use DATALINES for large amounts of data. You read directly from an external file instead. Check out the INFILE statement.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.