Problem in creating SAS dataset with set statement

[Haroon]

Hi;

I have imported a SPSS dataset into SAS. It is a big dataset, but initially I want to work on only one district,but I am unable to create a small dataset having only district level data because of format problem. The variable having names of districts is numeric in the original dataset but when I want to use IF statement, the log says Character values have been converted to numeric values. 

DATA DISTRICT;
     SET MYDATA.MICSDAT;
     IF HH7='BAHAWALPUR';
RUN;

I have also tried Proc Datasets to eliminate format and also tried to convert this variable HH7 to a charachter variable but thngs are not working.

any suggestion, where i am going wrong?

Thanks

Haroon

[Reeza]

Please post a proc contents of your data as well as a proc freq of the variable, without formats. 

Proc contents data=mydata.micsdat;
Run;

Proc freq data=mydata.micsdat;
Table hh7;
Format hh7;
Run;

[Haroon]

Proc Content output

[Reeza · Accepted solution]

Your variable HH7 is numeric - with the HH7A format applied to it. 

You need to find the code that matches the formatted value and filter via that method. 

Or convert variable to character to filter. 

*this will display the district coding by extracting info from format. In SPSS this would have been your code table values. ;
Proc format cntlout=fmts;
Run;

Proc print data=fmts;
Where upper(fmtname)='HH7A';
Var fmtname start label;
Run;

Or use the following where clause in your original query:

Put(hh7, hh7a.) = "DISTRICT";

[Haroon]

Thanks Reeza  but PUT function is not working with Where statement. syntax is correct but log says syntax error.

In the second option I have extracted the format name and labels, but what to do next to get the subset of larger data? If i should merge this fmts data with larger data and then filter by district?

[Reeza]

Figure out what number corresponds to your district and change your query to use that number instead of name. 

Or fix your where statement, the theory is correct. 

[Haroon]

Thanks a lot Reeza your suggestion worked good for me. The obervations under HH7 are coded and I have succeded to filter them but they appear in Print in the form of District names.

[Haroon]

output of Proc freq

[Reeza]

Yes, the format controls appearance. You can use the format from proc freq to remove format. Or proc datasets to remove it permanently.