topic Re: importing CSV file sas dataset in SAS Enterprise Guide

importing CSV file sas dataset

reddy19 — Wed, 25 May 2011 23:01:45 GMT

Hi,
I'm a beginner,i have to import csv file and make some changes and send the output to CSV file.Could somebody send the code.

And also we have to apply these conditions,the order should be refferal,approval and ship.If approval comes first, we have to switch the values of approval and refferal(In line 7 of data approval came first,we have to switch the values based on
Milestone_Date)

Here is example data:

DRC_CASE_ID Milestone_Date Product MILESTONE_TYPE

24386200 1/18/2010 GH COMPETITOR Referral
6200 1/20/2010 GH COMPETITOR Approval
24386200 2/2/2010 GH COMPETITOR Ship
24386200 3/11/2010 GH COMPETITOR Ship
24386200 5/6/2010 GH COMPETITOR Ship
24386200 6/17/2010 GH COMPETITOR Ship
33822730 12/27/2007GH COMPETITOR Approval
33822730 2/4/2009 GH COMPETITOR Referral
33822730 1/12/2010 GH COMPETITOR Ship
33822730 2/5/2010 GH COMPETITOR Approval
33822730 2/11/2010 GH COMPETITOR Ship
33822730 3/25/2010 GH COMPETITOR Ship

Re: importing CSV file sas dataset

Doc_Duke — Fri, 27 May 2011 20:32:31 GMT

In EGuide, you can use file --> import to bring in the .csv file to a SAS dataset, then you can use the Query Builder to manipulate the data, and file--> export to send it back out again.

I would recommend "SAS for dummies" or "the Little SAS book for EGuide x.x" to get you going on the data transformations, as it is rather difficult to narrate the interactive process of data transformations.

Re: importing CSV file sas dataset

reddy19 — Fri, 27 May 2011 20:44:31 GMT

i know how to do that in EG...but i have to know how to apply conditions for above scenario in query builder.

Re: importing CSV file sas dataset

Doc_Duke — Sat, 28 May 2011 20:40:26 GMT

You probably need to provide a sample of the desired output. I can't tell which fields you want to swap values for. Since you have duplicates of the milestone_type, you need to tell what you want to do with those too. This may not be something for query builder; it may need some other combination of procedures and/or data steps. If you only have one of each, this could be done with a combination of transpose (split columns) and query builder.

Message was edited by: Doc@Duke

Re: importing CSV file sas dataset

reddy19 — Mon, 30 May 2011 02:50:59 GMT

For Perticular id,For first date status=Approval and for second
date status=Refferal we have to swipe the values for status.

Here is the results look.

33822730 12/27/2007GH COMPETITOR Refferal
33822730 2/4/2009 GH COMPETITOR Approval.
33822730 1/12/2010 GH COMPETITOR Ship
33822730 2/5/2010 GH COMPETITOR Approval
33822730 2/11/2010 GH COMPETITOR Ship
33822730 3/25/2010 GH COMPETITOR Ship

Re: importing CSV file sas dataset

Patrick — Mon, 30 May 2011 10:27:56 GMT

data have;
infile datalines dsd dlm=' ' truncover;
input DRC_CASE_ID:$8. Milestone_Date:mmddyy. Product:$20. MILESTONE_TYPE:$8.;
format Milestone_Date mmddyy10.;
datalines;
24386200 1/18/2010 GH_COMPETITOR Referral
6200 1/20/2010 GH_COMPETITOR Approval
24386200 2/2/2010 GH_COMPETITOR Ship
24386200 3/11/2010 GH_COMPETITOR Ship
24386200 5/6/2010 GH_COMPETITOR Ship
24386200 6/17/2010 GH_COMPETITOR Ship
33822730 12/27/2007 GH_COMPETITOR Approval
33822730 2/4/2009 GH_COMPETITOR Referral
33822730 1/12/2010 GH_COMPETITOR Ship
33822730 2/5/2010 GH_COMPETITOR Approval
33822730 2/11/2010 GH_COMPETITOR Ship
33822730 3/25/2010 GH_COMPETITOR Ship
;
run;

proc sort data=have;
by DRC_CASE_ID Milestone_Date;
run;

data ReferralSecond (keep=DRC_CASE_ID);
set have;
by DRC_CASE_ID;
if first.DRC_CASE_ID then counter=0;
counter+1;
if counter=2 and upcase(MILESTONE_TYPE)='REFERRAL' then output;
run;

data want(drop=counter);
merge have ReferralSecond (in=RS);
by DRC_CASE_ID;
if first.DRC_CASE_ID and RS and upcase(MILESTONE_TYPE)='APPROVAL' then
do;
counter=0;
MILESTONE_TYPE=propcase('REFERRAL');
end;
counter+1;
if counter=2 and upcase(MILESTONE_TYPE)='REFERRAL' then MILESTONE_TYPE=propcase('APPROVAL');
run;

proc print data=want;
run;

HTH
Patrick