topic Re: Need Help with a complicated data step in SAS Programming

Need Help with a complicated data step

D_Z_ — Wed, 14 Sep 2016 20:57:17 GMT

Hi All!

I need some help! I am feeling not so smart right now. I don't know where to even begin... and that is why i am reaching out to the SAS community.

I am in the process of building a report to capture data ftrom a 3rd party vendor. With this vendor i have to download the file in CSV format (I have attached a copy of the raw data that is dumped from their system).

The report is survey data... so the subtotals currently existing in row 15 of the CSV for example, In C15 of the CSV, it shows 9.833... what i need SAS to do.. is count the # of survey's in B15... so count basically everything that has a 1-10 in that section so i have a total count of survey's. The Same thing across the subtotal line ofr all the questions.... so instead of a blank cell... i want SAS to count the applicable rows to show me a number. There are 5 questions total that i need to do this on.

For some background... the names and line numbers will change daily... they will never be static.. .because different agents work everyday and one agent can get more or less than the day before.

Can anyone see if they can take a stab at this and see if you can possible help me here?

Thanks,

Re: Need Help with a complicated data step

D_Z_ — Wed, 14 Sep 2016 21:00:49 GMT

I re-read my post and i better clarify.. i don't want to send info back to excel.. but i want the calculations to be done in SAS directly... i used the cells in the attached file for reference only.

Re: Need Help with a complicated data step

Reeza — Wed, 14 Sep 2016 21:06:18 GMT

1. What do you want as output?

2. Please post a CSV instead of xls, use the txt extension

You may have to read it all in as text or do a conditional read in, but generally well formatted. The comments may be an issue, it depends on how the software exports them.

Re: Need Help with a complicated data step

D_Z_ — Wed, 14 Sep 2016 21:12:14 GMT

On the SAS output... i want SAS to put out the subtotal line for each agent... and the # of survey's with the score that is already there for all 5 questions.

I have attached the .txt file.

Re: Need Help with a complicated data step

Cynthia_sas — Wed, 14 Sep 2016 22:42:29 GMT

Do you have a program that reads this data successfully? It does not look like a CSV file to me.

cynthia

Re: Need Help with a complicated data step

D_Z_ — Wed, 14 Sep 2016 22:51:47 GMT

Yes... that's beause someone asked for it as a text file... if you look at the original xls file... that is exactly how the data looks in .csv format. I already have a import process that works just fine to bring the data into the system.

DZ

Re: Need Help with a complicated data step

Reeza — Wed, 14 Sep 2016 23:55:20 GMT

Ok. Well let's not recreate the wheel.

Why not start off with the file you've already imported. Try attaching that as a zip or search on here how,to,create a datastep out of it.

Re: Need Help with a complicated data step

Reeza — Wed, 14 Sep 2016 23:55:21 GMT

Ok. Well let's not recreate the wheel.

Why not start off with the file you've already imported. Try attaching that as a zip or search on here how,to,create a datastep out of it.

Re: Need Help with a complicated data step

Kurt_Bremser — Thu, 15 Sep 2016 06:54:30 GMT

Post the original CSV file instead.

- nobody in his right mind opens Excel files from the web

- we can that exactly recreate the steps you need to do to arrive at your intended result

Re: Need Help with a complicated data step

ballardw — Thu, 15 Sep 2016 15:05:59 GMT

@D_Z_ wrote:
Yes... that's beause someone asked for it as a text file... if you look at the original xls file... that is exactly how the data looks in .csv format. I already have a import process that works just fine to bring the data into the system.

DZ

If I get a csv file, open in Excel and then save as Xls or xlsx, I seldom have exactly what was in the csv file. Excel will "helpfully" change some values into dates that shouldn't be, remove leading zeroes and I suspect other things that I have not discovered yet.

Re: Need Help with a complicated data step

ballardw — Thu, 15 Sep 2016 15:09:01 GMT

@D_Z_ wrote:

On the SAS output... i want SAS to put out the subtotal line for each agent... and the # of survey's with the score that is already there for all 5 questions.

I have attached the .txt file.

DZ

Personally I would go back to the survey vendor and get a data file and not a report file. Especially since I don't see how those "subtotals" reflect the "values" above them.

Re: Need Help with a complicated data step

D_Z_ — Thu, 15 Sep 2016 15:10:14 GMT

I get that... however.. .this board won't let me send a .csv file. So I cannot send it... its not the import i am looking for help on... its the data step... everyone is hung up on the actual import... but what i am looking to do is a datastep to add up the # of survey's in between the intial name and subset of "namd"... When i open the CSV file.. the general format is exactly the same as the .xls file i uploaded to begin with. I can't send it up in the format i am uploading in... it won't let me... it throws an error and tells me that it won't accept the file type and takes the attachment out.

Re: Need Help with a complicated data step

D_Z_ — Thu, 15 Sep 2016 16:25:42 GMT

Going back to the survey vendor is not possible.. .already have tried... so back to my original question... is there a way to have SAS count the # of instances in the BLANK subtotal cells... ?

Re: Need Help with a complicated data step

Jim_G — Thu, 15 Sep 2016 19:07:29 GMT

Something like this might work. jim

1. Import the data

2. eliminate blank lines, non data lines.

data want; set;

rowcnt+1;

/* repeat for all 10 questions */;

t1+ans1; t2+ans2; t3+ans3; .............. /* sum answers; */;

if agent=:'subtot' then do;

if ans1=. then ans1=t1/rowcnt;

if ans2=. then ans2=t2/rowcnt;

if ans3=. then ans3=t3/rowcnt;

if ans10=. then ans10=t10/rowcnt;

output;

ans1=0; ans2=0; ans3=0; ........rowcnt=0;

end;

if last.question then do; output; count=0; end;

proc print; id agent question; run;

Re: Need Help with a complicated data step

Tom — Fri, 16 Sep 2016 00:40:37 GMT

That is a pretty simple file to read. Normal report type layout.

You need to read the first column (or two) and check whether you are reading the report heading, patient heading, sub total or total lines. You can then read the rest of the row based on what type of row it is.

For the values that you read from the header lines save them into variables that you retain.

When you read in a detail line the OUTPUT the line.

You probably do not need to read the sub total and total lines since you should be able to calculate them from the detail lines. But if you did then just read them and write the to a different dataset. Looks like they are not "sub-totals" but actually means.

data want;
  infile "&path/&fname" dsd dlm='09'x truncover  ;
  length Department Person $100 ;
  length rowid 8 answer1 value1 answer2 value2
                 answer3 value3 answer4 value4 8
                 answer5 value5list $30 value5 8 answer6 $30
  ;
  length Questionnaire DateRange ReportDate $100 ;
  length x1 x2 $2000 ;
  retain Questionnaire DateRange ReportDate Department Person ;
  input x1 x2 @1 @ ;
  if _n_=1 then do;
    input / Questionnaire / DateRange / ReportDate ;
  end;
  if x1 = ' ' or x2='Answer chosen for #1' or x1='Total' then delete;
  else if x1=: 'Subtotal' then do;
    if person=' ' then Department=' ';
    else Person=' ';
  end;
  else if x2=' ' then do;
    if Department = ' ' then input Department;
    else input Person ;
  end;
  else do ;
    input rowid -- answer6;
    output ;
  end;
  drop x1 x2 ;
run;

proc print ;
  by Department Person notsorted;
  id rowid ;
  var answer1 -- answer6 ;
run;

Re: Need Help with a complicated data step

Peter_C — Fri, 16 Sep 2016 10:43:34 GMT

Does this not need a simple proc print with a SUM statement
Or is it time for a tabulate to show just those summary totals
??

Re: Need Help with a complicated data step

Kurt_Bremser — Mon, 19 Sep 2016 05:40:34 GMT

@D_Z_ wrote:

I get that... however.. .this board won't let me send a .csv file. So I cannot send it... its not the import i am looking for help on... its the data step... everyone is hung up on the actual import... but what i am looking to do is a datastep to add up the # of survey's in between the intial name and subset of "namd"... When i open the CSV file.. the general format is exactly the same as the .xls file i uploaded to begin with. I can't send it up in the format i am uploading in... it won't let me... it throws an error and tells me that it won't accept the file type and takes the attachment out.

If your import is already working, then post a sample of your dataset as a data step with datalines:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

That way you only have to copy/paste text, not upload a file.