I have a dataset with values like the below:
F1 | F2 | F2_1 | F3 | F3_1 | F3_2 |
Name1 | RD | 10 | RS | 1 | 2 |
Name2 | RD | 20 | RS | 3 | 4 |
Name2 | RD | 20 | RS | 5 | 6 |
Name2 | RD | 20 | RS | 7 | 8 |
Name3 | RD | 30 | RS | 11 | 12 |
Name3 | RD | 30 | RS | 13 | 14 |
Name4 | RD | 40 | RS | 15 | 16 |
and I need to create a text file with "|" delimited. And the output should like this:
Output:
Name1
RD|10
RS|1|2
Name2
RD|20
RS|3|4
RS|5|6
RS|7|8
Name3
RD|30
RS|11|12
RS|13|14
Name4
RD|40
RS|15|16
How to achieve this? Could someone please help?
The main thing is field F3 (RS) should have as many rows as in the input and rest should come only once. Thanks
Seems pretty simple. Looks like you just want to write two extra lines at the beginning of each group.
Let's converty your table into an actual SAS dataset.
data have ;
input F1 $ F2 $ F2_1 F3 $ F3_1 F3_2 ;
cards;
Name1 RD 10 RS 1 2
Name2 RD 20 RS 3 4
Name2 RD 20 RS 5 6
Name2 RD 20 RS 7 8
Name3 RD 30 RS 11 12
Name3 RD 30 RS 13 14
Name4 RD 40 RS 15 16
;
Now it looks like the first three variables define the groups. So use a BY statement.
data _null_;
file 'myfile.txt' dsd dlm='|';
set have ;
by f1 f2 f2_1;
if first.f2_1 then put f1 / f2 f2_1;
put f3 f3_1 f3_2;
run;
Hi,
data _null_; set have; length tmp $200; file "<path to file>\<outputfilename>.dlm"; put f1; tmp=catx("|",f2,f2_1); put tmp; tmp=catx("|",f3,f3_1,f3_2); put tmp; run;
Not tested (post test data in the form of a datastep!).
data have;
input F1$ F2$ F2_1$ F3$ F3_1$ F3_2$;
datalines;
Name1 RD 10 RS 1 2
Name2 RD 20 RS 3 4
Name2 RD 20 RS 5 6
Name2 RD 20 RS 7 8
Name3 RD 30 RS 11 12
Name3 RD 30 RS 13 14
Name4 RD 40 RS 15 16
;
proc sort data=have;
by F1;
run;
data want;
set have;
by F1;
file "path\filename.txt"
if first.F1 then do;
var=F1;output;
var=catx("|", F2, F2_1);output;
var=catx("|", F3, F3_1, F3_2);output;
end;
else do;
var=catx("|", F2, F2_1);output;
var=catx("|", F3, F3_1, F3_2);output;
end;
run;
Thanks! This worked!!
I just want an extended version of this output with two more variables. Let's say I have the dataset like the below.
data have;
input F1$ F2$ F2_1$ F4$ F4_1$ F4_2$ F3$ F5$;
datalines;
Name1 RD 10 RS 1 2 AS GS
Name2 RD 20 RS 3 4 AS TS
Name2 RD 20 RS 5 6 AS TT
Name2 RD 20 RS 7 8 AS ST
Name3 RD 30 RS 11 12 AS GS
Name3 RD 30 RS 13 14 AS HG
Name4 RD 40 RS 15 16 AS GS
;
Is it possible to get an output like the below. There is one more variable which has more than one value for one Name and that the order is also changed.
Name1
RD|10
RS|1|2
AS
GS
Name2
RD|20
RS|3|4
RS|5|6
RS|7|8
AS
TS
TT
ST
Name3
RD|30
RS|11|12
RS|13|14
AS
GS
HG
Name4
RD|40
RS|15|16
AS
GS
Thanks in advance!
Seems pretty simple. Looks like you just want to write two extra lines at the beginning of each group.
Let's converty your table into an actual SAS dataset.
data have ;
input F1 $ F2 $ F2_1 F3 $ F3_1 F3_2 ;
cards;
Name1 RD 10 RS 1 2
Name2 RD 20 RS 3 4
Name2 RD 20 RS 5 6
Name2 RD 20 RS 7 8
Name3 RD 30 RS 11 12
Name3 RD 30 RS 13 14
Name4 RD 40 RS 15 16
;
Now it looks like the first three variables define the groups. So use a BY statement.
data _null_;
file 'myfile.txt' dsd dlm='|';
set have ;
by f1 f2 f2_1;
if first.f2_1 then put f1 / f2 f2_1;
put f3 f3_1 f3_2;
run;
Thanks!!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: