BookmarkSubscribeRSS Feed
D_Z_
Obsidian | Level 7

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

 

16 REPLIES 16
D_Z_
Obsidian | Level 7

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.

Reeza
Super User

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.

 

D_Z_
Obsidian | Level 7

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

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

cynthia
D_Z_
Obsidian | Level 7
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
Reeza
Super User

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. 

Reeza
Super User

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. 

Kurt_Bremser
Super User

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

ballardw
Super User

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

 

 

D_Z_
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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

ballardw
Super User

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

D_Z_
Obsidian | Level 7
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... ?
Jim_G
Pyrite | Level 9

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;

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
  • 16 replies
  • 2155 views
  • 0 likes
  • 8 in conversation