BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hua
Obsidian | Level 7 hua
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Oligolas
Barite | Level 11

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/...

 

 

 

 

________________________

- Cheers -

hua
Obsidian | Level 7 hua
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

hua
Obsidian | Level 7 hua
Obsidian | Level 7

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

Patrick
Opal | Level 21

@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;
hua
Obsidian | Level 7 hua
Obsidian | Level 7

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!

Patrick
Opal | Level 21

@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 

 

hua
Obsidian | Level 7 hua
Obsidian | Level 7

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;

Tom
Super User Tom
Super User

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.

hua
Obsidian | Level 7 hua
Obsidian | Level 7

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!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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