topic Re: Pasting code from excel to sas in SAS Procedures

Pasting code from excel to sas

brophymj — Mon, 06 Jul 2015 11:18:03 GMT

I'm reading in a txt file into sas and write up the fields and attributes in columns in excel and then copy and paste into sas. However, the columns don't line up in sas. Is there a way to paste the data so that it looks like...

driver_cat $10.;

age $5.;

....

instead of

driver_cat $10.;

age $5.;

Re: Pasting code from excel to sas

RW9 — Mon, 06 Jul 2015 11:24:46 GMT

Why would you want to be copying code from and Excel file in the first place? Integrated Development Environments and text editors are the tools for coding.

Your problem lies in the fact that Excel has cells, nothing else has this concept. When you copy it takes the text out of those cells, puts a space in between and then keeps it on the clipboard. When you paste this text then gets pasted in the SAS text file. I know that some of the more modern IDEs allow textual alignments to markers (a bit like Word where you can move the ticks at the top and align text to that), but at least up to 9.3 I don't think this was possible. My suggestion - avoid using Excel for anything.

Re: Pasting code from excel to sas

brophymj — Mon, 06 Jul 2015 11:28:26 GMT

Are there text editor tools in SAS?

Re: Pasting code from excel to sas

Ksharp — Mon, 06 Jul 2015 11:50:08 GMT

If you are using SAS University Edition, there is a tool button named 'Formate code' to make your code readable .

Re: Pasting code from excel to sas

brophymj — Mon, 06 Jul 2015 12:34:21 GMT

I have sas 9.3 - is there something there where you can just paste a columns of fields and then another columns of attributes in such a way that they are aligned.

Thanks

Re: Pasting code from excel to sas

RW9 — Mon, 06 Jul 2015 13:07:59 GMT

There is the Enhanced Editor and basic Editor. Neither of which has alignment controls other than to put spaces instead of tabs. What are you trying to do though? If its just a parameter list, put it in a text file and %include or infile it in, doesn't seem to be much point in copy/pasting it.

Re: Pasting code from excel to sas

Reeza — Mon, 06 Jul 2015 13:50:54 GMT

EG also has a format your code option.

TWo other things to try:

1. Cut and paste each column using ALT key. I think this works but not in front of SAS to try.

2 Create the appropriate text in Excel using Concatenate and length functions before pasting to SAS.

Re: Pasting code from excel to sas

Kurt_Bremser — Mon, 06 Jul 2015 13:54:08 GMT

Import the Excel file into SAS, and then use formatted output to write a file with the correct spacings. You can then copy/paste from there.

Re: Pasting code from excel to sas

TomKari — Mon, 06 Jul 2015 16:09:15 GMT

Whenever I need to do this, I do it in Excel.

Assuming that my variable name is in cell A1, and my length in B1, the formula

=MID(A1&REPT(" ",20),1,20)&B1

should give you attractive output. Substitute whichever number looks good for the two instances of 20.

Tom