SL_no | Column | End result | |
1 | A078 B D BLEDSOE | ||
2 | DRAWINGS NOT CHANGED | A078 B D BLEDSOE DRAWINGS NOT CHANGED | |
3 | A050 D A MATHISON | ||
4 | OWNERSHIP OF | ||
5 | APPLICABLE | A050 D A MATHISON OWNERSHIP OF APPLICABLE | |
6 | A070 B D BLEDSOE | ||
7 | DRAWINGS NOT | A070 B D BLEDSOE DRAWINGS NOT |
1 and 2 row is one group
3,4,5, are another group
6 and 7 are another group
Please help me
Do you have exactly 7 rows of data in your data set? If not, you will need to provide rules based on the content of your variable that tells which ones need to be combined.
Do you have exactly 7 rows of data in your data set? If not, you will need to provide rules based on the content of your variable that tells which ones need to be combined.
You've marked the question as solved so I'm assuming no further responses are required.
Did you originally read this from a text file?
Do you know if the pattern of A### is an identifier for the start of each record? If so, this is pretty easy but if the pattern changes it's not as easy.
@Sharath_naik wrote:
SL_no Column End result 1 A078 B D BLEDSOE 2 DRAWINGS NOT CHANGED A078 B D BLEDSOE DRAWINGS NOT CHANGED 3 A050 D A MATHISON 4 OWNERSHIP OF 5 APPLICABLE A050 D A MATHISON OWNERSHIP OF APPLICABLE 6 A070 B D BLEDSOE 7 DRAWINGS NOT A070 B D BLEDSOE DRAWINGS NOT
1 and 2 row is one group
3,4,5, are another group
6 and 7 are another group
Please help me
Thanks for Replying @Reeza . 🙂
#A### is a partern ...#A or #B or #C will be generated going forward....
It's not .txt, IT;s a mainframe data,... But in Sorted order
You can use PRXMATCH to detect the 'new group of records' start condition.
Use a retained result string to accumulate a concatenation of each row in the group.
Example (generate some sample data)
data have; call streaminit(123); do rownum = 1 to 1000; length mfrecord $25; /* mainframe record */ if rand('uniform') < 0.25 or rownum in (1, 101,102,103) then do; mfrecord = byte(64+rand('integer',26)) || put(rand('integer',999),z3.) || ' AAAAAAAAA' ; seq = 1; end; else do; seq + 1; mfrecord = repeat(byte(64+seq), rand('integer',0,24)); end; OUTPUT; end; keep rownum mfrecord; run;
Example (compute result as a group aggregate that is concatenation of rows)
Version 1. One result row for each group
data want; set have end=done; length result $1000; retain result; if prxmatch('/^[A-Z]\d+ /', mfrecord) then do; if _n_ > 1 then OUTPUT; call missing(result); end; result = catx(' ', result, mfrecord); if done then OUTPUT; keep result; run;
Version 2. Concatenation result on last record in group
Requires lead processing due to synthetic group definition
data want; /* MERGE with NO BY variables is 1:1 merge and provides a LEAD variable 'nextrecord' */ merge have have(firstobs=2 rename=mfrecord=nextrecord) end=done ; donex = done; length accum $1000; retain accum; length result $1000; if prxmatch('/^[A-Z]\d+ /', nextrecord) then do; result = catx(' ', accum, mfrecord); OUTPUT; group_id + 1; call missing(accum, result); end; else do; accum = catx(' ', accum, mfrecord); if done then result = accum; OUTPUT; end; keep rownum mfrecord result group_id donex nextrecord; run;
Aggregating can be simplified if you code a grouping view first
data groups; set have; if prxmatch('/^[A-Z]\d+ /', mfrecord) then groupno+1; run; data want; do until (last.groupno); set groups; by groupno; length accum result $1000; accum = catx(' ', accum, mfrecord); if last.groupno then result = accum; output; end; drop accum; run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: