Is there any way to separate multiple records from one cell into one record per row?

[LL5]

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

[mkeintz · Accepted solution]

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:

1. Use the COUNTW function to find the number of "words" in invoicelist (where a word in this case is delimited by commas).
2. Use the SCAN functino to retrieve those words in succession.

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;

[LL5]

Thanks! I'm curious if I have huge amounts of data, do I have to put them all below the datalines statement?

[SASKiwi]

You don't use DATALINES for large amounts of data. You read directly from an external file instead. Check out the INFILE statement.

[LL5]

I see. Got it. Thanks.