Desktop productivity for business analysts and programmers

Suggestions On Difficult Import

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Suggestions On Difficult Import

Hi All,

I am working on an SAS import project that has me a little stumped and I would like to get some suggestion on what direction I should go with this.

I have attached a spreadsheet, which is my raw data.  What i am trying to do is turn this into 3 datasets.  There are 3 questions from this one sheet that i need to pull the data out from.  I have a limitation in where the main SAS server is Linux in my corporation, which limits the ways i can accomplish this.  Process Import is not an option for me right now.

The thing that is screwing me up is this.  The three questions do not start on a static row, as the agents listed will change day over day.  This makes it a little more difficult.  The three questions i need to sort out are:

Chat Custom Variable by Operator for How would you rate the last agent you chatted with on being professional and courteous?(survey69440225)

Chat Custom Variable by Operator for Please rate how well the last representative you chatted with addressed your questions and concerns today. (survey2089029)

Chat Custom Variable by Operator for Thinking about this chat experience, how would you rate the level of service you received from the last representative you chatted with? (survey0068022)

I know SAS has the power to pull out the data I need, however, I am not sure exactly what direction to go with this.  This is way more advanced than what I am normally trying to accomplish.  Can someone give me a helping hand with some direction on how you think i should go about this.

Basically at the end of the day, what I want to end up doing is taking report... uploading to my network folder and running a process to let SAS pull the data out I need as there is alot of data on this page that i will never look at nor need.  Just those 3 questions is all i want to pull out.  I can get them in static datasets... that's no issue... its a matter of how i get the data i need to pull out of the spreadsheet that i need to figure out.

I am using SAS Enterprise Guide 5.1 with the SAS 9.3 engine.

Thanks in advance!


Accepted Solutions
Solution
‎08-21-2013 05:50 PM
PROC Star
Posts: 1,143

Re: Suggestions On Difficult Import

Hi, D.Z.

Here's a pattern that I've had considerable success with over the years. It splits the problem into two discrete pieces of code:

1. Figure out if we're dealing with data we want, and if we are, set a flag. Do this for each distinct type of data we want.

2. For each type of data we want, look at the record, and do whatever we need to get the output we want. When we know we've finished with this type, set the flag off.

Give it a spin, see what you think.

Tom

data work.ProfessionalCourteousResps work.AddressedQuestionResps work.LevelOfServiceResps;
retain ProfessionalCourteousFlag AddressedQuestionFlag LevelOfServiceFlag 0;
set WORK.SAMPLE_IMPORT (rename=(F1=Operator F2=Item1 F3=Item10 F4=Item2 F5=Item3 F6=Item4 F7=Item5 F8=Item6 F9=Item7 F10=Item8 F11=Item9 F12=Total_Recorded));

/* Figure out which set of questions we're working on */

if Operator = "Chat Custom Variable by Operator for How would you rate the last agent you chatted with on being professional and courteous?(survey69440225)"
then ProfessionalCourteousFlag = 1;
else if Operator = "Chat Custom Variable by Operator for Please rate how well the last representative you chatted with addressed your questions and concerns today. (survey2089029)"
then AddressedQuestionFlag = 1;
else if Operator = "Chat Custom Variable by Operator for Thinking about this chat experience, how would you rate the level of service you received from the last representative you chatted with? (survey0068022)"
then LevelOfServiceFlag = 1;

/* Deal with survey69440225 records */

if ProfessionalCourteousFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.ProfessionalCourteousResps;
if Operator = "Total" then ProfessionalCourteousFlag = 0;
end;

/* Deal with survey2089029 records */

if AddressedQuestionFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.AddressedQuestionResps;
if Operator = "Total" then AddressedQuestionFlag = 0;
end;

/* Deal with survey0068022 records */

if LevelOfServiceFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.LevelOfServiceResps;
if Operator = "Total" then LevelOfServiceFlag = 0;
end;

drop ProfessionalCourteousFlag AddressedQuestionFlag LevelOfServiceFlag;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,143

Re: Suggestions On Difficult Import

Hi, D.Z.

Yeah, I gotta vote with you on this one, you didn't pick an easy one!

In terms of framing a solution, are you comfortable with writing SAS code and running it from the program editor in Enterprise Guide?

Tom

Contributor
Posts: 70

Re: Suggestions On Difficult Import

I am ok with writing code as long as I have good examples to draw off of.  I am by no means an expert... but can follow direction fairly well.

Most of my more recent projects involve writing SQL to pull data from our databases, so i am somewhat familiar with basic SAS code (our main databases are in Oracle so the code is a bit different for oracle).

Frequent Contributor
Posts: 129

Re: Suggestions On Difficult Import

D.Z.,

Can you source the data which was the input to this spreadsheet?  If so, look at using that, rather than the spreadsheet, which is obviously a report.

If not then do you have Access to PC files?  If so, then an option would be to create range names for each of the presentation tables in the spreadsheet.

Finally if you do not have Access to PC Files,  you might try the following:

1.  Understand the organiztion of the tables in your spreadsheet.  It looks like there is some regularity which might guide your processing.  By that I mean it looks like there are different tables which have the same column format and number of rows.  Detemine how you can programmatically recognize the different styles.

2.  Save the file as a csv file.

3.  Simplest but brute force approach would be to build multiple datasteps which read one table at a time (you could use first obs and last obs  dataset options for this).  Then combine the tables as needed.

4.  A more elegant solution might be to read the entire file but using different input statements for the different tables and output to a different dataset depending upon the table.

I would be tempted to start with my first approach.  If this is to be repeatable, then I would look at the more programatic approach in order to make more roboust.

HTH,

Larry

Contributor
Posts: 70

Re: Suggestions On Difficult Import

I wish I could source the data... but unfortunately, its not possible with the vendor we use and I do not have Access to PC Files.

Part of the simplicity to this project is being able to get the file in email and copy it directly to a network location so SAS can pick up the file.  With the people that will be running this... I have to make it to where there is no manipulation required of the end user... just copy and paste the file and then run the project...

This makes this project very difficult.  I believe #3 is the way i need to go.  Our company uses a server with Linux for its main server, so i cannot write out code to import as the location of the file has to be on a general network location.

Can you point me to some sample data somewhere so i can see the syntax of what the code (Data Steps) would look like.  This is way more advanced than what i am used to dealing with.

PROC Star
Posts: 1,143

Re: Suggestions On Difficult Import

Okay, the programming part will be a little odd, but I think you'll be okay with it.

First, though, you need to get your spreadsheet into SAS. Do you know any way to do this from your Linux SAS server? Unfortunately, I don't have access to a *ix environment, so I'm not in a position to advise. It looks like the easiest way is with the Access to PC files product. Do you have it available?

Another option is to save the sheet as .csv. Would this be workable for you?

And lastly, as Larry suggests, can you access to source data for this spreadsheet? That might provide much more useful info.

Tom

Contributor
Posts: 70

Re: Suggestions On Difficult Import

Larry,

I have the import of the spreadsheet covered.  I can get the data into a SAS dataset without issue.  It's once I import the data... figuring out how to pull out just the data i need.

DZ

Frequent Contributor
Posts: 129

Re: Suggestions On Difficult Import

DZ,

How are you doing this?  proc import from excel, proc import from csv, libname statement, etc.

Also can you provide a sample of what the data looks like after you have imported into a SAS data set?  Don't need everything -- proc contents and proc print of 20 obs or so would be sufficent.  It would be helpful to see how the data is organized.

lw:-)

PROC Star
Posts: 1,143

Re: Suggestions On Difficult Import

Yup, I'm with Larry, that's exactly what we need to see.

Tom

Contributor
Posts: 70

Re: Suggestions On Difficult Import

Sorry guys... I should of put that in my last response.  I am using the import wizard to pull the worksheet into SAS.  The wizard works fine... its when i try to use program code that it won't work because of the Linux limitation.

DZ

Frequent Contributor
Posts: 129

Re: Suggestions On Difficult Import

DZ,

Okay, so you are using the import wizard in EG to read the spreadsheet into a dataset.  It still would be helpful for us to understand how that dataset is structured -- column names and types and some sample records in the dataset.  I can think of several ways the dataset could look that the import wizard is creating and  don't want to propose a solution to an incorrectly assumed case.

So please post either proc contents and subset proc print or simply a subset of the data set which can be attached.

thx,

lw:-)

Contributor
Posts: 70

Re: Suggestions On Difficult Import

Here is a screenshot of how the data looks:

**note that i added labels for the column names but just did the minimum to get the info in there so you could see what the data looks like**

import_capture.PNG

Here is the process code from the import wizard:

/* --------------------------------------------------------------------

   Code generated by a SAS task

  

   Generated on Wednesday, August 21, 2013 at 2:17:57 PM

   By task:     Import Data Wizard

  

   Source file: C:\Users\work\Documents\sample import.xls

   Server:      Local File System

  

   Output data: WORK.sample_import

   Server:      SASApp

  

   Note: In preparation for running the following code, the Import

   Data wizard has used internal routines to transfer the source data

   file from the local file system to SASApp. There is no SAS code

   available to represent this action.

   -------------------------------------------------------------------- */

/* --------------------------------------------------------------------

   This DATA step reads the data values from a temporary text file

   created by the Import Data wizard. The values within the temporary

   text file were extracted from the Excel source file.

   -------------------------------------------------------------------- */

DATA WORK.sample_import;

    LENGTH

        F1               $ 140

        F2               $ 4

        F3               $ 5

        F4               $ 4

        F5               $ 4

        F6               $ 4

        F7               $ 4

        F8               $ 4

        F9               $ 4

        F10              $ 4

        F11              $ 4

        F12              $ 14 ;

    LABEL

        F1               = "Operator"

        F2               = "1"

        F3               = "10"

        F4               = "2"

        F5               = "3"

        F6               = "4"

        F7               = "5"

        F8               = "6"

        F9               = "7"

        F10              = "8"

        F11              = "9"

        F12              = "Total_Recorded" ;

    FORMAT

        F1               $CHAR140.

        F2               $CHAR4.

        F3               $CHAR5.

        F4               $CHAR4.

        F5               $CHAR4.

        F6               $CHAR4.

        F7               $CHAR4.

        F8               $CHAR4.

        F9               $CHAR4.

        F10              $CHAR4.

        F11              $CHAR4.

        F12              $CHAR14. ;

    INFORMAT

        F1               $CHAR140.

        F2               $CHAR4.

        F3               $CHAR5.

        F4               $CHAR4.

        F5               $CHAR4.

        F6               $CHAR4.

        F7               $CHAR4.

        F8               $CHAR4.

        F9               $CHAR4.

        F10              $CHAR4.

        F11              $CHAR4.

        F12              $CHAR14. ;

    INFILE '/saswork/SAS_work4EDE000021DB_csfnysasprod01/#LN00023'

        LRECL=246

        ENCODING="LATIN1"

        TERMSTR=CRLF

        DLM='7F'x

        MISSOVER

        DSD ;

    INPUT

        F1               : $CHAR140.

        F2               : $CHAR4.

        F3               : $CHAR5.

        F4               : $CHAR4.

        F5               : $CHAR4.

        F6               : $CHAR4.

        F7               : $CHAR4.

        F8               : $CHAR4.

        F9               : $CHAR4.

        F10              : $CHAR4.

        F11              : $CHAR4.

        F12              : $CHAR14. ;

RUN;

Frequent Contributor
Posts: 129

Re: Suggestions On Difficult Import

DZ,

Okay, I thought that might be how the data looked.   I have not been able to re-generate that but can now offer a couple of methods:

1.  Going back to the range names I mentioned earlier.  Define three ranges which refer to the data for the three questions you want to look.  Yes this involves modifications to the excel file,  but it significantly reduces the coding you need to do.  Using the import wizard, In tab 1 of 2 set a distinct dataset name. Then on Tab 2 of 4 you need to select predefined named range and legt SAS rename columns to comply with SAS naming conventions.  Try that to see if  it works.

2.  I can provide some example code to read the data as you seem to have it, but it is going to be complex.  Something like this might work to identify the survey69440225 items:

data  survey69440225 ;

  set your_input_data ;

     rename f1 = operator

                f2  = item2

                f3 = item10

                f4 = item2;

                .

               .

               f11 = item9

               ;

     if f1 contains '69440225' then do until upcase(f1) = 'TOTAL' ;

          set your_input_data ;

           if upcase(f1)  not in  ('TOTAL','OPERATOR')   then output ;

           else stop ;

     end;

run ;

This basically cycles through your data until it finds the header for desired table.  It then skips the column headers record, reads each of the data records and outputs to dataset with new names.  And then stops when entire table has been read.

Treat #2 as pseudo code.  It is untested, but the logic should hold.

HTH

Larry

Contributor
Posts: 70

Re: Suggestions On Difficult Import

Larry,

I have taken a stab at this and failed miserably.

I have tried this... and i cannot get it to work... i am using this as a data step and i keep getting a funky error...but of course i cannot find anything similar when i google the error that gives me enough information to understand why the error came up.

Here is the code i am using in the datastep which is based off of your code:

data WORK.Professional_courteous;

if Agent contains '69440225' then do until upcase(Agent) = 'TOTAL';

if upcase(Agent) not in ('TOTAL','Agent')then output;

else stop;

end;

run;

When i run this, I get the following error log... and i obviously have done something incorrectly.

1                                                          The SAS System                           14:04 Wednesday, August 21, 2013

1          ;*';*";*/;quit;run;

2          OPTIONS PAGENO=MIN;

3          %LET SYSLAST=WORK.SAMPLE_IMPORT;

4          %LET _CLIENTTASKLABEL='Program';

5          %LET _CLIENTPROJECTPATH='';

6          %LET _CLIENTPROJECTNAME='';

7          %LET _SASPROGRAMFILE=;

8         

9          ODS _ALL_ CLOSE;

10         OPTIONS DEV=ACTIVEX;

11         GOPTIONS XPIXELS=0 YPIXELS=0;

12         FILENAME EGSR TEMP;

13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=MeadowPrinter

13       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/MeadowPrinter.css") NOGTITLE

13       ! NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

14        

15         GOPTIONS ACCESSIBLE;

16         data WORK.Professional_courteous;

17         if Agent contains '69440225' then do until upcase(Agent) = 'TOTAL';

                    ________

                    388

                    76

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

18         if upcase(Agent) not in ('TOTAL','Agent')then output;

19         else stop;

20         end;

           ___

           161

ERROR 161-185: No matching DO/SELECT statement.

21         run;

NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).

      18:11  

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.PROFESSIONAL_COURTEOUS may be incomplete.  When this step was stopped there were 0 observations and 1

         variables.

WARNING: Data set WORK.PROFESSIONAL_COURTEOUS was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

     

22        

23         GOPTIONS NOACCESSIBLE;

24         %LET _CLIENTTASKLABEL=;

25         %LET _CLIENTPROJECTPATH=;

26         %LET _CLIENTPROJECTNAME=;

27         %LET _SASPROGRAMFILE=;

28        

29         ;*';*";*/;quit;run;

30         ODS _ALL_ CLOSE;

31        

32        

2                                                          The SAS System                           14:04 Wednesday, August 21, 2013

33         QUIT; RUN;

34        

Thanks,

DZ

Solution
‎08-21-2013 05:50 PM
PROC Star
Posts: 1,143

Re: Suggestions On Difficult Import

Hi, D.Z.

Here's a pattern that I've had considerable success with over the years. It splits the problem into two discrete pieces of code:

1. Figure out if we're dealing with data we want, and if we are, set a flag. Do this for each distinct type of data we want.

2. For each type of data we want, look at the record, and do whatever we need to get the output we want. When we know we've finished with this type, set the flag off.

Give it a spin, see what you think.

Tom

data work.ProfessionalCourteousResps work.AddressedQuestionResps work.LevelOfServiceResps;
retain ProfessionalCourteousFlag AddressedQuestionFlag LevelOfServiceFlag 0;
set WORK.SAMPLE_IMPORT (rename=(F1=Operator F2=Item1 F3=Item10 F4=Item2 F5=Item3 F6=Item4 F7=Item5 F8=Item6 F9=Item7 F10=Item8 F11=Item9 F12=Total_Recorded));

/* Figure out which set of questions we're working on */

if Operator = "Chat Custom Variable by Operator for How would you rate the last agent you chatted with on being professional and courteous?(survey69440225)"
then ProfessionalCourteousFlag = 1;
else if Operator = "Chat Custom Variable by Operator for Please rate how well the last representative you chatted with addressed your questions and concerns today. (survey2089029)"
then AddressedQuestionFlag = 1;
else if Operator = "Chat Custom Variable by Operator for Thinking about this chat experience, how would you rate the level of service you received from the last representative you chatted with? (survey0068022)"
then LevelOfServiceFlag = 1;

/* Deal with survey69440225 records */

if ProfessionalCourteousFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.ProfessionalCourteousResps;
if Operator = "Total" then ProfessionalCourteousFlag = 0;
end;

/* Deal with survey2089029 records */

if AddressedQuestionFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.AddressedQuestionResps;
if Operator = "Total" then AddressedQuestionFlag = 0;
end;

/* Deal with survey0068022 records */

if LevelOfServiceFlag then
do;
if substr(Operator, 1, 3) = "ICS" then output work.LevelOfServiceResps;
if Operator = "Total" then LevelOfServiceFlag = 0;
end;

drop ProfessionalCourteousFlag AddressedQuestionFlag LevelOfServiceFlag;
run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 958 views
  • 6 likes
  • 3 in conversation