DATA Step, Macro, Functions and more

How to export .txt file by group

Accepted Solution Solved
Reply
Contributor hua
Contributor
Posts: 36
Accepted Solution

How to export .txt file by group

Hello all,

 

I tried to export the data by group to text file (.txt), but still have some trouble to do so. Hope someone in the SAS community groups can help. I would greatly appreciate it! Thanks a lot!

I have a dataset like this:

stn time var1 var2 var3
1 1      
1 2      
1 3      
1 4      
1 5      
2 1      
2 2      
2 3      
2 4      
2 5      

I want to export the data by stn, and named input1.txt. And inside each file, drop stn, year and the header (the first line). What I want to get in each file is only the several varibles without header.

 

Thanks a lot!

 

Regards,

Hua


Accepted Solutions
Solution
4 weeks ago
Super User
Super User
Posts: 6,499

Re: How to export .txt file by group

[ Edited ]

You really haven't given enough information to know exactly what you want. You should have posted what you want the output files to look like.

 

You can use the FILEVAR= option on the FILE statement to allow a data step to generate different files based on data in the dataset.

data _null_;
  set have ;
  by stn ;
  length filevar $200;
  filevar=cats('state',stn,'.txt');
  file out filevar=filevar dsd ;
  put time var1-var3 ;
run;

If you want to use different names for the files then change the logic that creates the filename.

Now if you wanted to generate CSV files then add the DSD option to the FILE statement. If you wanted delimited files using something other than comma as the delimiter then also add the DLM= option to specify something other than comma as the delimiter.

It you don't want to sort the data by STN first then make sure to include the MOD option on the FILE statement.

View solution in original post


All Replies
Frequent Contributor
Posts: 103

Re: How to export .txt file by group

Hi,

 

I think the main difficulty you have, is that you don't look in the forum if someone had a similar problem.

https://communities.sas.com/t5/SAS-Procedures/PROC-EXPORT-file-txt/td-p/24622

https://communities.sas.com/t5/Base-SAS-Programming/Export-large-dataset-to-multiple-txt-files/td-p/...

 

 

 

 

________________________

- That still only counts as one -

Contributor hua
Contributor
Posts: 36

Re: How to export .txt file by group

Thanks! I read the questions and solutions, but still don't know how to deal with my question. I tried to edit the code for my data, but still cannot get the results.

Super User
Super User
Posts: 7,400

Re: How to export .txt file by group

Post test data in the form of a datastep.  As such this is just a guess:

proc sort data=have out=loop (keep=stn) nodupkey;
  by stn;
run;
data _null_;
  set loop;
  call execute('data _null_;  
                  set have (where=(stn="'||strip(stn)||'"));
                  file "c:\input_'||strip(stn)||'.txt";
                  put time var:;
                run;');
run;

This creates a datastep for each group which outputs the data from that group to a file called input_<stn>.txt.

Contributor hua
Contributor
Posts: 36

Re: How to export .txt file by group

Thanks! But this got an error said "where clause requires compatible variables" and I could not get the txt file.

Respected Advisor
Posts: 3,887

Re: How to export .txt file by group

@hua

One way to go:

proc sort data=sashelp.class out=work.class;
  by sex;
run;

proc sql noprint;
  select name into :varlist separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='CLASS'
  ;
quit;

data _null_;
  set work.class;
  by sex;
  _outfile=cats('c:\temp\test_',sex,'.txt');
  file _dummy_ filevar=_outfile delimiter='|';
  put &varlist;
run;
Contributor hua
Contributor
Posts: 36

Re: How to export .txt file by group

Thank you very much! This example works! But I don't know what does the "select name into :varlist separated by ' '
from dictionary.columns " mean? So I don't know how to apply this to my data. Could you please tell me more details, it will be really helpful for me! Thanks!

Respected Advisor
Posts: 3,887

Re: How to export .txt file by group

@hua

The Proc SQL  INTO clause allows you to query a table or view and store the result strings in a SAS macro variables.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1y2jszlvs4hugn14...

 

Dictionay tables contain data about your data.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p11h9yhja6t25an1mkx8...

 

dictionary.columns contains all the columns in all the tables and views for which you have a libref defined.

where libname='WORK' and memname='CLASS' restricts the query to table WORK.CLASS

select name into :varlist separated by ' ' stores a blank separated list of all the values in column name into macro variable &varlist 

 

Contributor hua
Contributor
Posts: 36

Re: How to export .txt file by group

Thanks for these information. I tried the sql clause, but I got a blank results without any error or warning.
data have;
  input stn time v1 v2 v3;
datalines;
1 1 1.1 54 2
1 2 2.3 .  1.8
1 3 3.1 32 0
1 4 4.2 48 .
1 5 5.4 52 0
2 1 1.5  . 0
2 2 2.7 53 0
2 3 3.0  . 4
2 4 4.2  . 0
2 5 5.5 55 3
;
run;

proc sort data=have;
  by stn time;
run;

proc sql;
  select name into :varlist separated by ' '
  from dictionary.columns
  where libname='Work' and memname='have'
  ;
quit;

Solution
4 weeks ago
Super User
Super User
Posts: 6,499

Re: How to export .txt file by group

[ Edited ]

You really haven't given enough information to know exactly what you want. You should have posted what you want the output files to look like.

 

You can use the FILEVAR= option on the FILE statement to allow a data step to generate different files based on data in the dataset.

data _null_;
  set have ;
  by stn ;
  length filevar $200;
  filevar=cats('state',stn,'.txt');
  file out filevar=filevar dsd ;
  put time var1-var3 ;
run;

If you want to use different names for the files then change the logic that creates the filename.

Now if you wanted to generate CSV files then add the DSD option to the FILE statement. If you wanted delimited files using something other than comma as the delimiter then also add the DLM= option to specify something other than comma as the delimiter.

It you don't want to sort the data by STN first then make sure to include the MOD option on the FILE statement.

Contributor hua
Contributor
Posts: 36

Re: How to export .txt file by group

Sorry for the incomplete information.Right, I should put more details next time.

This code works very well, and I finally got what I want!

Thank you very much!!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 217 views
  • 3 likes
  • 5 in conversation