DATA Step, Macro, Functions and more

Need Help with a complicated data step

Reply
Contributor
Posts: 70

Need Help with a complicated data step

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,

DZ

 

Contributor
Posts: 70

Re: Need Help with a complicated data step

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.

Super User
Posts: 19,810

Re: Need Help with a complicated data step

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.

 

Contributor
Posts: 70

Re: Need Help with a complicated data step

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

SAS Super FREQ
Posts: 8,866

Re: Need Help with a complicated data step

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

cynthia
Contributor
Posts: 70

Re: Need Help with a complicated data step

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
Super User
Posts: 19,810

Re: Need Help with a complicated data step

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. 

Super User
Posts: 19,810

Re: Need Help with a complicated data step

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. 

Super User
Posts: 7,782

Re: Need Help with a complicated data step

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Need Help with a complicated data step


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.

 

 

Contributor
Posts: 70

Re: Need Help with a complicated data step

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.

Super User
Posts: 7,782

Re: Need Help with a complicated data step


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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Need Help with a complicated data step


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.

Contributor
Posts: 70

Re: Need Help with a complicated data step

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... ?
Frequent Contributor
Posts: 95

Re: Need Help with a complicated data step

[ Edited ]

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;

Ask a Question
Discussion stats
  • 16 replies
  • 989 views
  • 0 likes
  • 8 in conversation