Move an observation to the next variable column

[Ramsha]

Suppose I have set variable names: City, State, Zip from a csv import! Not datalines. 

New York  NY 11001

Baltimore  MD 12231

Raleigh     NC 32435

In observation 3, the NC was incorrectly formatted to the City variable which thus moved the Zip into the State variable. How do you fix or format an imported csv/excel? 

[Reeza]

Ideally you fix this in the import process. Is that possible here?

---

@Ramsha wrote:

Suppose I have set variable names: City, State, Zip from a csv import! Not datalines. 

New York  NY 11001

Baltimore  MD 12231

Raleigh     NC 32435

In observation 3, the NC was incorrectly formatted to the City variable which thus moved the Zip into the State variable. How do you fix or format an imported csv/excel? 

---

[Ramsha]

Hi, 

I'm actually not sure how to fix that in the import process. Do you have any steps available? 

[Reeza]

How did you import the data? Did you write a PROC IMPORT or a DATA STEP or use the point and click commands?

Do you know where the error originates? Is it in the raw data or is it being read incorrectly?

---

@Ramsha wrote:

Hi, 

I'm actually not sure how to fix that in the import process. Do you have any steps available? 

---

[Ramsha]

I did a simple proc import. 

PROC IMPORT DATAFILE=CSV
        OUT=Places
        DBMS=CSV
        REPLACE;

RUN;

I understand the issue with the Proc Import but unfortunately I can only use proc import in my situation. Is there any other way to manipulate the data? 

[Reeza]

I understand the issue with the Proc Import but unfortunately I can only use proc import in my situation. Is there any other way to manipulate the data? 

Not easily. PROC IMPORT assumes types, so in this case if you have the same type you can correct it manually but that means checking each record systematically and knowing ahead of time all the possible/impossible values for the State/ZIP and doesn't preclude other issues. 

The optimal solution is to use the data step code with the DSD option. 

An alternative is use a data step to read it in but read each line as a single character and manually process it. Again, this would not use PROC IMPORT.  

If you can attach a sample of your file it would help but if you're stuck using PROC IMPORT for whatever reason, you're stuck fixing each record manually AFAIK. 

[Reeza]

I suspect you need the DSD option in the data step, but not sure if that can be added via PROC IMPORT.

[ballardw]

---

@Ramsha wrote:

Suppose I have set variable names: City, State, Zip from a csv import! Not datalines. 

New York  NY 11001

Baltimore  MD 12231

Raleigh     NC 32435

In observation 3, the NC was incorrectly formatted to the City variable which thus moved the Zip into the State variable. How do you fix or format an imported csv/excel? 

---

With lots of routine and tedious code to fix stupid data entry errors.

If there are only 3 variables involved and the data were only US and protectorates I would be tempted to settle for finding the zip code and then using the ZIP related functions SAS has to get the city and state:

Zip = scan(catx(' ',city,state,zip),-1);

city = scan(zipcity(zip),1,',');

state = zipstate(zip);

This assumes that your ZIP as recorded is accurate and that your sashelp.zipcode data is reasonably current.

Otherwise lots of string checking and validating as you pull pieces from here and there with functions such as Scan after identifying likely type of error: Zip in state field, city and state in City;

                              Zip and State in State field,

Don't forget someone may have gotten real creative and managed to either duplicate or place part of City name in the State field, or recorded a City with a comma as part of the original City such a New, Town (which would mean zip value is field AFTER the expected ZIP)