Export Distinct rows to distinct csv

[Jassim]

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;

[s_lassen]

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.

[yabwon]

Works great, I've tested it.
I would consider adding "options nonotes;" before datastep and "options notes;" after - for big files with multiple outputs, just to keep the log clean.

All the best
Bart

[Tom]

---

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

[ed_sas_member]

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;

[Reeza]

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;

---

[bhaiterabhai]

Unique and Distinct are two SQL constraints. The main difference between Unique and Distinct in SQL is that Unique helps to ensure that all the values in a column are different while Distinct helps to remove all the duplicate records when retrieving the records from a table. shareit vidmate

[flisterfour]

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 

[andreas_lds]

Sorry, but i don't see a question .... The code posted by @s_lassen  should work in your situation, too.

[Reeza]

You're code is using backticks not quotation marks. That's valid in other languages but not SAS. Are you using SQL pass through?