topic quick & dirty way to cut & paste data from excel into SAS editor? in SAS Programming

quick & dirty way to cut & paste data from excel into SAS editor?

RobF — Fri, 09 Jan 2015 20:12:15 GMT

I have to run Chi-squared tests on a series of small datasets contained in Excel spreadsheets.

Is there an easy way to simply cut and paste the cells containing the data from Excel directly into a data input statement in the SAS editor?

I'm running into formatting issues with tabs and character length formats. I've tried copying & pasting the data from Excel to Notepad first, then copying from Notepad to the SAS editor, but no luck.

Here's an example of inpatient count data listed by hospital in an Excel spreadsheet:

PROVIDER	Q1Q2_2011_denom	Q1Q2_2011_numer	Q3Q4_2013_denom	Q3Q4_2013_numer
CAPITAL_CITY_HOSPITAL	102	4	321	7
ST_PAULS	129	8	253	9
UNIVERSITY_MEDICAL_CENTER	95	7	162	16

which I want to cut & paste into SAS as follows:

data hospital_data;

input PROVIDER $50. Q1Q2_2011_denom Q1Q2_2011_numer Q3Q4_2013_denom Q3Q4_2013_numer;

cards;

CAPITAL_CITY_HOSPITAL 102 4 321 7

ST_PAULS 129 8 253 9

UNIVERSITY_MEDICAL_CENTER 95 7 162 16

;

run;

However the output data in hospital_data lumps the provider name and the inpatient count data together under PROVIDER and leaves a blank in the Q1Q2_2011_denom column.

Any thoughts how to expedite quick & dirty cut and pastes from Excel to SAS?

Thanks!

Re: quick & dirty way to cut & paste data from excel into SAS editor?

PGStats — Sat, 10 Jan 2015 03:26:21 GMT

Add a colon before the informat $50.

input PROVIDER :$50. Q1Q2_2011_denom Q1Q2_2011_numer Q3Q4_2013_denom Q3Q4_2013_numer;

That way you will be reading PROVIDER with formatted list input, i.e. the input field will end at the first space encountered.

Re: quick & dirty way to cut & paste data from excel into SAS editor?

art297 — Sat, 10 Jan 2015 03:48:36 GMT

You might find some useful code at: Copy and Paste from Excel to SAS - sasCommunity

Re: quick & dirty way to cut & paste data from excel into SAS editor?

RobF — Sat, 10 Jan 2015 14:46:11 GMT

Thank you for the suggestions.

In fact, I've also found that cutting and pasting data from Excel into this editor window for a new SAS Discussion question automatically formats the data. I can then cut paste from the editor window directly into a data step & SAS creates a correctly formatted dataset.

data hospital_data;

length provider $50.;

input PROVIDER $ Q1Q2_2011_denom Q1Q2_2011_numer Q3Q4_2013_denom Q3Q4_2013_numer;

cards;

…

run;

Re: quick & dirty way to cut & paste data from excel into SAS editor?

Tom — Sat, 10 Jan 2015 23:14:35 GMT

Personally I just copy and paste and then issue a change command to change the tabs to |.

Then in the DATA step add an INFILE statement to let SAS know to use DSD option.

input cards dsd dlm='|' truncover;