Hello,
I have a dataset in SAS that I am exporting to a delmited file (not a CSV). Is it possible to specify which lines to delimit? There are some lines which I do not want to delimit, as the file is being read incorrectly by another program and the delimiter (a space) may cause the files to be read in wrong by the other program.
Here's an example of the dataset:
Indicator | Var1 | Var2 | Var3 | Var4 | Var5 | Var6 | Var7 | Var8 | Var9 |
0001 | |||||||||
1116 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
1116 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
1216 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0117 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0217 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0317 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
END | |||||||||
0002 | |||||||||
1116 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
1216 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0117 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0217 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0317 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
END | |||||||||
0003 | |||||||||
1116 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
1216 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0117 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0217 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0317 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
The 'END' and '0001, 0002, 0003, etc' specify the end of a grouping and the start of the next grouping. '0001, 0002, etc' ranges from 1-1001, but lets pretend these increase infinitely. the "1116, 1116, 1216, 0117, 0217, 0317" are dates (mmyy) and run up to 1166, but this could also change. When exporting to a txt file, I need the spaces that are being inserted as delimiters between var1-var9 to NOT be placed in the observations that have "end" or grouping number present.
Any help would be greatly appreciated.
Thanks
-Jbscholten
<Rant>
First, having done a fair amount of work around Y2K issues once upon a time I would suggest to whoever is using that file structure that they have a significant problem with 2 digit years. Coupled with your 0001 = Jan 2000 possibly confusion this is heading for a significant failure at some point.
</Rant>
Because of the aforementioned 0001 issue you really cannot rely on the value.
You also did not specify what the delimiter should be. And do you need INDENTS or is that an artifact or what ever you are copying from?
Add a FILE statement to data _null_ below, set the length for the STR variable, indicate the delimiter and this may work if indents are needed.
data have; infile datalines truncover; input Indicator $ Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 ; datalines; 0001 1116 1 2 3 4 5 6 7 8 9 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END 0002 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END 0003 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END ; run; data _null_; set have; length str $100; /* long enough to hold all of the variables plus the delimiters*/ if _n_=1 or Indicator='END' or lag(indicator)='END' then put indicator; else do; str= catx('|',indicator,of var:); put str; end; run;
You need to use a manual data step, and then you have full control of the file with the PUT statements.
BY groups will allow you to identify the groups you need.
Post sample input data, I'm assuming this is output, and sample output that align. You can include only 3 or 4 variables to make it easier. Then we can mock up some code to show you what htis may look like.
Untested sketch of what this may look like:
data _null_;
file 'path to output file';
set dataset;
by group;
if _n_ = 1 then do;
put 'indicator' 'var1' 'var2' 'var3' 'var4' ... 'var9';
end;
if first.group then do;
put group;
end;
put var1-var9;
if last.group then do;
put 'END';
end;
run;
<Rant>
First, having done a fair amount of work around Y2K issues once upon a time I would suggest to whoever is using that file structure that they have a significant problem with 2 digit years. Coupled with your 0001 = Jan 2000 possibly confusion this is heading for a significant failure at some point.
</Rant>
Because of the aforementioned 0001 issue you really cannot rely on the value.
You also did not specify what the delimiter should be. And do you need INDENTS or is that an artifact or what ever you are copying from?
Add a FILE statement to data _null_ below, set the length for the STR variable, indicate the delimiter and this may work if indents are needed.
data have; infile datalines truncover; input Indicator $ Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 ; datalines; 0001 1116 1 2 3 4 5 6 7 8 9 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END 0002 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END 0003 1116 1 2 3 4 5 6 7 8 9 1216 1 2 3 4 5 6 7 8 9 0117 1 2 3 4 5 6 7 8 9 0217 1 2 3 4 5 6 7 8 9 0317 1 2 3 4 5 6 7 8 9 END ; run; data _null_; set have; length str $100; /* long enough to hold all of the variables plus the delimiters*/ if _n_=1 or Indicator='END' or lag(indicator)='END' then put indicator; else do; str= catx('|',indicator,of var:); put str; end; run;
This seems to work; however, in my data set all of the values in var1-9 are in the format 12.34. In my previous code (with the delimiter column), I specified var1-9 as z5.2 to keep leading and trailing zeros, so that 1.2 is written as 01.20 in the export file.
How can I keep this when using the data _null_ statement?
Used this code to keep 0's and only delimit appropriate lines:
Set work.clearbreak;
Length str $100; /* long enough to hold all of the variables plus the delimiters*/
If _n_=1 or Indicator='END' or lag(indicator)='END' then put indicator;
Else do;
str = catx(' ',indicator, vvalue(VAR4), vvalue(VAR5), vvalue(VAR6), vvalue(VAR7), vvalue(VAR8), vvalue(VAR9), vvalue(VAR10), vvalue(VAR11), vvalue(VAR12));
Put str;
End;
Run;
ODS CSVALL is good for this scenario. ods csvall file='/folders/myfolders/xx.csv'; options nobyline; title '#byval1'; footnote 'END'; proc report data=sashelp.class noheader; by sex notsorted; run; ods csvall close;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.