Hello group. I am new to SAS and am trying to figure out the best way to split a table into multiple files. I got the code to select distinct values in a row as well as the count of distinct items in a row. Ultimately I would like to export all columns related to a unique value in the "make" column into a file with the same name as the "make". I would like to perform this for each unique "make" in the data set. For example query SASHELP.CARS and export out all Acura info to the Acura file, Honda info to the Honda file, Chevy info to the Chevy file, etc....
/*counts unique*/ PROC SQL; SELECT count(distinct(Make)) into :makecount FROM SASHELP.CARS; quit; %put &makecount; /*creates single column table using only unique names*/ proc sql; Create table Work.query as select distinct(Make) from SASHELP.CARS; run; quit;
You can just use a data step and the FILEVAR option, something like
/* Delete current .csv files, if any */
x 'del mydir\*.csv';
data _null_;
set sashelp.cars;
outfile=cats('mydir\',make,'.csv');
file out filevar=outfile dsd delimiter=';' mod;
put /* here goes the variable list */;
run;
I do not have SAS on this computer, so I cannot test. But I think it may be necessary (if the input data is not sorted by MAKE) to use the MOD option on the FILE statement, as the output file may otherwise be rewritten each time the OUTFILE variable changes. Which is also why I put in a statement to delete any .csv files in the output directory, as the output would otherwise be added to the files if they exist already. But test it for yourself. Change the "mydir" part to wherever you want the files.
@s_lassen wrote:
You can just use a data step and the FILEVAR option, something like
/* Delete current .csv files, if any */ x 'del mydir\*.csv'; data _null_; set sashelp.cars; outfile=cats('mydir\',make,'.csv'); file out filevar=outfile dsd delimiter=';' mod; put /* here goes the variable list */; run;
I do not have SAS on this computer, so I cannot test. But I think it may be necessary (if the input data is not sorted by MAKE) to use the MOD option on the FILE statement, as the output file may otherwise be rewritten each time the OUTFILE variable changes. Which is also why I put in a statement to delete any .csv files in the output directory, as the output would otherwise be added to the files if they exist already. But test it for yourself. Change the "mydir" part to wherever you want the files.
You do not need the MOD option. If you did include MOD then if any of the files already exists before the data step then the lines written by this step will just be added to the end of those files.
Hi @Jassim
Here is another approach using the DOSUBL () function (<=> CALL EXECUTE), which enables the immediate execution of SAS code after a text string is passed:
proc sql outobs=3;
create table make_list as
select distinct(Make) as Make from sashelp.cars;
quit;
data _null_;
set make_list;
outfile_list = cats('','path/',Make,'.csv'); /*update path*/
rc = dosubl(cats('proc export data=sashelp.cars(where=(make = "',Make,'")) outfile="',outfile_list,'" dbms=csv replace; run;'));
run;
One way:
PROC SORT DATA=SASHELP.CARS OUT=CARS;
BY make;
RUN;
DATA _NULL_;
SET cars; *Dataset to be exported;
BY make; *Variable that file is to be split on;
*Create path to file that is to be exported;
if first.make then out_file=cats('/folders/myfolders/', trim(make));
file temp filevar=out_file dlm=',' dsd;
*If first value of make then output column names;
if first.make then
put 'Make, Model, MPG_HIGHWAY, MPG_CITY';
*Output variables;
put make model mpg_highway mpg_city;
run;
@Jassim wrote:
Hello group. I am new to SAS and am trying to figure out the best way to split a table into multiple files. I got the code to select distinct values in a row as well as the count of distinct items in a row. Ultimately I would like to export all columns related to a unique value in the "make" column into a file with the same name as the "make". I would like to perform this for each unique "make" in the data set. For example query SASHELP.CARS and export out all Acura info to the Acura file, Honda info to the Honda file, Chevy info to the Chevy file, etc....
/*counts unique*/ PROC SQL; SELECT count(distinct(Make)) into :makecount FROM SASHELP.CARS; quit; %put &makecount; /*creates single column table using only unique names*/ proc sql; Create table Work.query as select distinct(Make) from SASHELP.CARS; run; quit;
SELECT *
FROM `amc_info`
WHERE department =' (
SELECT DISTINCT department )
into outfile = 'Different department'
I have huge data in amc_info , with different department. One department may have more than one row, total department is more than 30, now i want to export this data with department-wise file name. https://forpc.onl/snaptube-for-pc jiofilocalhtml
Sorry, but i don't see a question .... The code posted by @s_lassen should work in your situation, too.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.