I have a tab delimited flat file that periodically contains structure shifts due to limitations of the software it is exported from. Example lines are below. The last line of the example data shows the problem. When data is missing (outside of the "header" columns), there is not a carriage return to go the next line. The next line just begins where the previous left off.   Name Code Hr Bid Department Called-In Abs Dept Seniority 27-JAN-2020 1 12-OCT-2019 / 2 8.000 A SMITH, JOHN P (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 29-JAN-2020 2 05-MAY-2018 / 2 8.000 A SMITH, JOHN R (999999) EMPLOYEE RELATIONS [3] PROCESSING [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 21-JAN-2020 3 13-JUN-2009 / 6 8.000 A SMITH, JOHN J (999999) MECHANICAL [3] MECHANICAL [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 20-JAN-2020 4 07-DEC-2006 / 2 8.000 A SMITH, JOHN O (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 19-JAN-2020 5 24-MAR-2001 / 8 8.000 A SMITH, JOHN C (999999) PROCESSING [3] PROCESSING [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 30-JAN-2020 6 03-NOV-2002 / 4 8.000 A SMITH, JOHN G (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 10-FEB-2020 Name Code Hr Bid Department Called-In Abs Dept Seniority 11-FEB-2020 1 22-NOV-2001 / 8 8.000 A SMITH, JOHN S (999999) PACKING [2] PACKING [2] I would like to be able to handle these situations within the infile read, but I have not been able to consistently / sustainably capture the issue. I am hoping there is a way detect that the current infile line is longer than expected, and thus "move" SAS to the next observation, e.g. the final SAS table should look like..    Name Code Hr Bid Department Called-In Abs Dept Seniority 27-JAN-2020 1 12-OCT-2019 / 2 8.000 A SMITH, JOHN P (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 29-JAN-2020 2 05-MAY-2018 / 2 8.000 A SMITH, JOHN R (999999) EMPLOYEE RELATIONS [3] PROCESSING [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 21-JAN-2020 3 13-JUN-2009 / 6 8.000 A SMITH, JOHN J (999999) MECHANICAL [3] MECHANICAL [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 20-JAN-2020 4 07-DEC-2006 / 2 8.000 A SMITH, JOHN O (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 19-JAN-2020 5 24-MAR-2001 / 8 8.000 A SMITH, JOHN C (999999) PROCESSING [3] PROCESSING [3] Name Code Hr Bid Department Called-In Abs Dept Seniority 30-JAN-2020 6 03-NOV-2002 / 4 8.000 A SMITH, JOHN G (999999) MECHANICAL [2] MECHANICAL [2] Name Code Hr Bid Department Called-In Abs Dept Seniority 10-FEB-2020 Name Code Hr Bid Department Called-In Abs Dept Seniority 11-FEB-2020 1 22-NOV-2001 / 8 8.000 A SMITH, JOHN S (999999) PACKING [2] PACKING [2] I've tried combinations of missover / truncover / flowover, as well as _infile_ variable, but I can't seem to get the pointer to move along to the next SAS observation while still within 1 row of the input file. ... View more

I have a CSV file that contains a field that is a comma separated list. An example would be   C1 C2 C3 C4 Tab1 DB1 S1 "A,B,C,D,E" Tab2 DB1 S1 "A,B,C" Tab3 DB2 S1 "D,E" Tab4 DB2 S3 "A,B,C,D,E,G" The output desired would be   C1 C2 C3 C4 Tab1 DB1 S1 A Tab1 DB1 S1 B Tab1 DB1 S1 C Tab1 DB1 S1 D Tab1 DB1 S1 E Tab2 DB1 S1 A Tab2 DB1 S1 B Tab2 DB1 S1 C etc... I am currently able to read the data into SAS with the 4 variables, and then split the C4 variable using SCAN function.    However, I'm curious if this could be handled in the input data step, instead of doing it in two separate steps. Given the size of the input file being in the hundreds of millions of rows, only having to read through the data once would be very helpful.   I have tried a few variations of the construct..   data t; infile "naming_test.csv" missover dlm="," dsd; length NAME $ 50; input NAME $ @ ; var = 0; do while (name ne ' '); var+1; /* keep track of a counter for testing */ output; input name $ @; end; run; However, I haven't been able to crack it. Thanks for your help! ... View more

I'm attempting to extract a substring from various areas of a string.   It would be on lines that have "save $x w/purchase" and would be looking for the quantity required to buy. In line 1, it would be "4". In line 8 in would be "6" The price after "save" can be double digit dollars (i.e. $25.00) and the quantity required can also be double digits. Sometimes it appears after "purchase/", somethings "purchase/any". I guess the easiest way to boil it down would be the first complete number (1, 5, 12, etc.) after the occurence of "w/".  It seems like a straight forward function/regex but I can't seem to get one to work with all the possible permutations of order and number of positions.     Sample data would be   data WORK.SAMPLE; infile datalines dsd truncover; input Offer:$194.; datalines4; save $1.00 w/purchase/any 4 or more mix & match save $0.73 w/Just For U (Limit: 1) save $1.00 w/purchase/any 4 participating items mix & match save $1.00 w/purchase/any 4 or more mix & match save $1.00 w/purchase/any 4 participating items mix & match "save $1.00 w/purchase/any 4 participating items mix & match, save $1.00 w/purchase/any 4 participating items mix & match" save $4.00 w/purchase/4 or more mix or match save $3.00 w/purchase/any6 participating items mix & match save $4.00 w/purchase/any 4 participating items mix & match save $3.00 (Limit: 5) save $5.00 w/purchase/5 participating items mix & match save $5.00 w/purchase/5 save $1.00 (Limit: 1) save $1.00 (Limit: 1) save $4.00 w/purchase/any 4 participating items mix & match save $4.00 w/purchase/any 4 participating items mix & match save $3.00 w/purchase/any 6 participating items mix & match save $0.50 w/purchase/any 4 or more participating items mix & match save $5.00 w/purchase/5 participating items mix & match save $5.00 w/purchase/any 5 participating items mix & match ;;;; ... View more