@athapa1183 wrote:
the files are just like the sample data.
The CLM are not missing.
Only some NTE segments are missing.
~ yes this is record separator but you can use any separator you like to get the results.
what is the rule to find the value of 1 out of "11+A+1"
ANS: there is no rule we just need that 1 from "11+A+1"
Thank You
I beg to differe that they are the same: Your A1.txt, if treating the ~ as a record separator looks like:
CLM*178*322.56***12+B+1*Y*A*Y*Y**NTE*19*94289*WER~ CLM*123 *322.56***11+A+1*Y*A*Y*Y* *90289*RRW~ CLM*223 *322.56***11+A+1*Y*A*Y*Y* NTE*11*93289*RRW~ CLM*120 *322.56***11+A+1*Y*A*Y*Y* NTE*17*91289*SEW~
Which looks not at all like your S1.txt "example" in regards to CLM and the ~ character.
And without a rule computers do not work. Rule <=> program code.
Since you are changing the "structure" of the data with each question, the actual desired elements and even the lengths of the desired values it is very hard to suggest any actual solution that fits this mess.
I ask again, where is the documentation of the layout of this file? If you don't have any, say so. Then go the source and get the documentation.
HI Ballardw,
I do not have the document.
I will try to get.
Thank YOu
@athapa1183 wrote:
i WANT :
CLM FRQ NTE AMT 15176002400002 1 11899 1260 18199000180002 1 87599 8377 12345678400002 1 1234 22121000180002 1 21
If we interpret the "~" as end-of-record character in file S1.txt, we find the data items of your "WANT" table in the records listed below (I omit the FRQ values, which are always in the same record as the CLM values):
Record | Item | Value |
1 | CLM | 15176002400002 |
9 | NTE | 11899 |
23 | AMT | 1260 |
31 | AMT | 8376.72 |
90 | CLM | 18199000180002 |
97 | NTE | 87599 |
112 | CLM | 12345678400002 |
133 | AMT | 1234 |
141 | AMT | 21 |
200 | CLM | 22121000180002 |
Does this mean that the order of CLM, NTE and AMT items belonging together is not fixed?
HI Yes the order is not fixed. CLM FRQ AND AMT are always there together but the NTE is missing sometimes and that is what is causing the problem.
Thank You
If the file is not broken into lines then tell SAS that so that it doesn't bother to look for line breaks. Use RECFM=N on the INFILE statement. Instead tell it that '~' is the delimiter between "words".
data check;
infile "&path/S1.txt" recfm=n dsd dlm='~';
length len word1 word2 8 line $400 ;
input line ;
len=lengthn(line);
word1=countw(line,'*');
word2=countw(line,'*','m');
if line in: ('CLM','NTE','AMT') then
put _n_ 5. len 6. word1 4. word2 4. +1 line= ;
run;
Looking at your S1 example file it looks the CLM, NTE and AMT "lines" always have the same number of tokens when you use '*' as the delimiter. So just parse out what part you want using SCAN().
_N_ LEN W1 W2 1 43 7 10 line=CLM*15176002400002*18104.49***11+A+1**A*Y*Y 6 8 3 3 line=AMT*F3*0 9 13 3 3 line=NTE*ADD*11899 23 10 3 3 line=AMT*D*1260 31 13 3 3 line=AMT*D*8376.72 90 37 7 10 line=CLM*18199000180002*78***13+A+1**A*Y*Y 94 8 3 3 line=AMT*F3*0 97 13 3 3 line=NTE*ADD*87599 112 43 7 10 line=CLM*12345678400002*18104.49***11+A+1**A*Y*Y 117 8 3 3 line=AMT*F3*0 133 10 3 3 line=AMT*D*1234 141 8 3 3 line=AMT*D*21 200 37 7 10 line=CLM*22121000180002*78***13+A+1**A*Y*Y 204 8 3 3 line=AMT*F3*0
data x;
infile 'c:\temp\a1.txt' dlm='*~';
input x : $40. @@;
run;
data temp;
set x;
if x in ('CLM' 'NTE') or lag(x) in ('CLM' 'NTE') then output;
run;
data temp;
set temp;
if x='CLM' then group+1;
run;
data clm(keep=group clm) nte(keep=group nte);
set temp;
if lag(x)='CLM' then do;clm=x;output clm;end;
if lag(x)='NTE' then do;nte=x;output nte;end;
run;
data want;
merge clm nte;
by group;
run;
Sorry this does not work
Try this one:
data x;
infile cards dlm='*~';
input x : $40. @@;
cards;
CLM*178*322.56***12+B+1*Y*A*Y*Y**NTE*19*94289*WER~
CLM*123 *322.56***11+A+1*Y*A*Y*Y* *90289*RRW~
CLM*223 *322.56***11+A+1*Y*A*Y*Y* NTE*11*93289*RRW~
CLM*120 *322.56***11+A+1*Y*A*Y*Y* NTE*17*91289*SEW~
;
run;
data temp;
set x;
if x in ('CLM' 'NTE') or lag(x) in ('CLM' 'NTE') then output;
run;
data temp;
set temp;
if x='CLM' then group+1;
run;
data clm(keep=group clm) nte(keep=group nte);
set temp;
if lag(x)='CLM' then do;clm=x;output clm;end;
if lag(x)='NTE' then do;nte=x;output nte;end;
run;
data want;
merge clm nte;
by group;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.