How to edit imported excel file?

[tomrvincent]

this might help you: https://analytics.ncsu.edu/sesug/2008/CC-028.pdf

[Ramsha]

The document is very helpful however when I try to apply this or write any code, I get a message saying that the variables have already been set. 

It says I should do it in the DATA step however where exactly should I put the codes? 

[tomrvincent]

Somewhere after you've imported the file.

[Tom]

Did you really start with an Excel file?  Or did you have a CSV file and your computer system "helpfully" automatically opened it in Excel instead of letting you edit it with a text editor?

This is what a CSV file for that type of data would look like.

Street,City,State/ZIP,Country
288 York Street,New Haven,CT 06511,USA
301-399 South Boulevard Drive,Bainbridge,GA 39819,USA
150-151 Tremont Street,Boston,MA 02111,USA
2395 Ingleside Avenue,Macon,GA 31204,USA
1007 Merchant Street,Ambridge,PA 15003,USA
859 Washington Avenue,Miami Beach,FL 33139,USA
974 Great Plain Avenue Needham MA 02492,USA, ,
139 Lynnfield Street,Peabody,MA 01960,USA
180 Nassau Street Princeton,NJ 08542,USA,
563 Carlsbad Village Drive,Carlsbad,CA 92008,USA

As you can see the line that starts with '974' is missing the comma before the STATE/ZIP value.

[Ramsha]

Hello, thank you so much! Now I understand the issue. I previously thought csv and excel files were the same. 

So I started writing the code but I am a beginner and do not know the procedure for infile. 

Can you please tell me how I should code this to be in that format above in the question? I started with this...but of course I know my syntax is wrong. 

data places;
infile "/folders/myshortcuts/myfolder/places.csv" dsd delimiter=none;
input Street $ City $ State_ZIP $ Country $;
run;
proc print data=places;
run;

[ballardw]

---

@Ramsha wrote:

Hello, thank you so much! Now I understand the issue. I previously thought csv and excel files were the same. 

So I started writing the code but I am a beginner and do not know the procedure for infile. 

Can you please tell me how I should code this to be in that format above in the question? I started with this...but of course I know my syntax is wrong. 

data places;
infile "/folders/myshortcuts/myfolder/places.csv" dsd delimiter=none;
input Street $ City $ State_ZIP $ Country $;
run;
proc print data=places;
run;

---

If the file is CSV then the delimiter really better be comma : delimiter=',' .

If a length is not assigned to character variables before use in an input statement they will default to 8 characters.

Provide a length statement BEFORE the input statement to set the length such as

Length Street $ 100.

If your commas do not actually align with the data description you have other issues to address such as running into the end of the data line before all the values are encountered. You are reading 4 variables in the above code but if there are not 3 commas to separate them SAS may attempt to read data from the following line so you need either MISSOVER or TRUNCOVER on the infile statement. If the values may have a comma imbedded in a specific field such as street address then likely should add the DSD option as well.