DATA Step, Macro, Functions and more

Proc export to CSV

Reply
Occasional Contributor
Posts: 11

Proc export to CSV

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

Super User
Posts: 6,936

Re: Proc export to CSV

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: Proc export to CSV

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 

 

Super User
Posts: 10,500

Re: Proc export to CSV

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.

Occasional Contributor
Posts: 11

Re: Proc export to CSV

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
Super User
Posts: 6,936

Re: Proc export to CSV

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: Proc export to CSV

I have two data sets,

Data set 1

AreaHigh Risk AlertRelease MonthDelivery LeadYearNumber of Project HoursPrimarykey
SAFALSEJulyNaresh,A201712SAFALSE2017
TCHighMayMadhu,Sudhan201725TCHigh2017
STCFALSEAugNaresh,A201745STCFALSE2017
SAFALSEJulyMadhu,Sudhan201765SAFALSE2017
TCHighMaySridhar,raju201612TCHigh2016
STCFALSEAugNaresh,A201652STCFALSE2016


Data set 2

AreaHigh Risk AlertRelease MonthManagerYearPrimaykey
SAFALSEJulyNandan, Krishna2017SAFALSE2017
TCHIGHMayNandan, Krishna2017TCHIGH2017
STCFALSEAugMadhu, Shankar2017STCFALSE2017
SAFALSEJulyMadhu, Shankar2017SAFALSE2017
TCHighMayMadhu, Shankar2017TCHigh2017
STCFALSEAugNandan, Krishna2017STCFALSE2017

 

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

AreaHigh Risk AlertRelease MonthDelivery LeadYearNumber of Project HoursPrimarykeyManager Name
SAFALSEJulyNaresh,A201712SAFALSE2017Nandan, Krishna
TCHighMayMadhu,Sudhan201725TCHigh2017Nandan, Krishna
STCFALSEAugNaresh,A201745STCFALSE2017Madhu, Shankar
SAFALSEJulyMadhu,Sudhan201765SAFALSE2017Nandan, Krishna
TCHighMaySridhar,raju201612TCHigh2016#N/A
STCFALSEAugNaresh,A201652STCFALSE2016#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;

 

 

AreaHigh Risk AlertRelease MonthDelivery LeadYearNumber of Project HoursPrimarykeyManager Name
SAFALSEJulyNaresh,A201712SAFALSE2017Nandan, Krishna
        
TCHighMayMadhu,Sudhan201725TCHigh2017Nandan, Krishna
STCFALSEAugNaresh,A201745STCFALSE2017Madhu, Shankar
SAFALSEJulyMadhu,Sudhan201765SAFALSE2017Nandan, Krishna
        
TCHighMaySridhar,raju201612TCHigh2016#N/A
STCFALSEAugNaresh,A201652STCFALSE2016#N/A
Super User
Posts: 6,936

Re: Proc export to CSV

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 156 views
  • 0 likes
  • 3 in conversation