topic Re: Proc export to CSV in SAS Programming

Proc export to CSV

anare — Tue, 20 Jun 2017 08:39:12 GMT

Hi Team,

I have two dataset called Raw_file and Schedule_report.

Requirement:- Need to perform Left join using primary key.

PROC SQL;
CREATE TABLE WORK.result AS
SELECT *
FROM WORK.Raw_file t1
LEFT JOIN Schedule_report t2 ON (t1.Primarykey = t2.Primarykey);
QUIT;

After executing the above code am getting accurate data. However while exporting to csv am getting unwanted blank rows are created in CSV file.

Can anyone help me how to resolve this issue..

Thanks

Naresh

Re: Proc export to CSV

Kurt_Bremser — Tue, 20 Jun 2017 08:51:34 GMT

Please supply example data. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert a dataset to a data step that can be posted here. Post using the {i} or "little running man" buttons to preserve formatting.

DO NOT post an Excel file or the raw .sas7bdat! Post SAS code, as only that can be used in any SAS environment to recreate your dataset!

Just enough lines from WORK.result (that cause the unexpected output in the csv) are sufficient.

Also post the code you use to export the data from WORK.result to the csv, and the log from it.

Re: Proc export to CSV

ballardw — Tue, 20 Jun 2017 14:37:39 GMT

Do any of your fields in either of those sets have a text field that might contain notes, comments or other free form text? If you data looks right before export it may be that the data contains line feed or carriage return characters which will appear as blank lines or misaligned text.

This might be addressable in the export but you'd have to show us what you used to do the export.

Re: Proc export to CSV

anare — Wed, 21 Jun 2017 09:22:09 GMT

Thanks for your refply.!!

I have coded like

Proc export data=Risk.Risk_history

outfile='/work/we21/users/ananre/risk/Risk.csv'

DBMS=CSV Replace;

run;

After executing the above code it extracts CSV However am getting blanks after 2 rows and after 4 rows respectively.

Please help me to resolve this issue

thanks

Re: Proc export to CSV

anare — Wed, 21 Jun 2017 09:47:36 GMT

Thanks for your reply.!!

I have coded like

Proc export data=Risk.Risk_history
outfile='/work/we21/users/ananre/risk/Risk.csv'
DBMS=CSV Replace;
run;

After executing the above code it extracts CSV However am getting blanks after 2 rows and after 4 rows respectively.

Please help me to resolve this issue

thanks

Re: Proc export to CSV

Kurt_Bremser — Wed, 21 Jun 2017 10:29:56 GMT

Without example data (see my previous post) there's nothing we can do to help.

Re: Proc export to CSV

anare — Wed, 21 Jun 2017 11:06:49 GMT

I have two data sets,

Data set 1

Area	High Risk Alert	Release Month	Delivery Lead	Year	Number of Project Hours	Primarykey
SA	FALSE	July	Naresh,A	2017	12	SAFALSE2017
TC	High	May	Madhu,Sudhan	2017	25	TCHigh2017
STC	FALSE	Aug	Naresh,A	2017	45	STCFALSE2017
SA	FALSE	July	Madhu,Sudhan	2017	65	SAFALSE2017
TC	High	May	Sridhar,raju	2016	12	TCHigh2016
STC	FALSE	Aug	Naresh,A	2016	52	STCFALSE2016

Data set 2

Area	High Risk Alert	Release Month	Manager	Year	Primaykey
SA	FALSE	July	Nandan, Krishna	2017	SAFALSE2017
TC	HIGH	May	Nandan, Krishna	2017	TCHIGH2017
STC	FALSE	Aug	Madhu, Shankar	2017	STCFALSE2017
SA	FALSE	July	Madhu, Shankar	2017	SAFALSE2017
TC	High	May	Madhu, Shankar	2017	TCHigh2017
STC	FALSE	Aug	Nandan, Krishna	2017	STCFALSE2017

Using primary key i have done left join using primary key my data sert looks like this

Area	High Risk Alert	Release Month	Delivery Lead	Year	Number of Project Hours	Primarykey	Manager Name
SA	FALSE	July	Naresh,A	2017	12	SAFALSE2017	Nandan, Krishna
TC	High	May	Madhu,Sudhan	2017	25	TCHigh2017	Nandan, Krishna
STC	FALSE	Aug	Naresh,A	2017	45	STCFALSE2017	Madhu, Shankar
SA	FALSE	July	Madhu,Sudhan	2017	65	SAFALSE2017	Nandan, Krishna
TC	High	May	Sridhar,raju	2016	12	TCHigh2016	#N/A
STC	FALSE	Aug	Naresh,A	2016	52	STCFALSE2016	#N/A

After exporting it to CSV my data extracts accuratly but unwanted blanks appears.

Proc export data= final_data

outfile="Path/final_data.csv";

DBMS=CSV Replace;

Run;

Area	High Risk Alert	Release Month	Delivery Lead	Year	Number of Project Hours	Primarykey	Manager Name
SA	FALSE	July	Naresh,A	2017	12	SAFALSE2017	Nandan, Krishna

TC	High	May	Madhu,Sudhan	2017	25	TCHigh2017	Nandan, Krishna
STC	FALSE	Aug	Naresh,A	2017	45	STCFALSE2017	Madhu, Shankar
SA	FALSE	July	Madhu,Sudhan	2017	65	SAFALSE2017	Nandan, Krishna

TC	High	May	Sridhar,raju	2016	12	TCHigh2016	#N/A
STC	FALSE	Aug	Naresh,A	2016	52	STCFALSE2016	#N/A

Re: Proc export to CSV

Kurt_Bremser — Wed, 21 Jun 2017 11:17:07 GMT

Read my post again with respect to how data should be posted. I won't start to type data from screen in my precious spare time.

Your post does NOT convey any pertinent information about data types, formats and other attributes.

If you do a join, post the code of the join.