BookmarkSubscribeRSS Feed
anare
Calcite | Level 5

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

7 REPLIES 7
Kurt_Bremser
Super User

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.

anare
Calcite | Level 5

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 

 

ballardw
Super User

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.

anare
Calcite | Level 5
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
anare
Calcite | Level 5

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
Kurt_Bremser
Super User

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.

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
  • 7 replies
  • 5141 views
  • 0 likes
  • 3 in conversation