topic Re: Formats error in SAS Programming

Formats error

robertrao — Tue, 23 Oct 2012 23:46:01 GMT

Hi Team,

I have a variable called "categorical" and I wrote an order format ($sort) for that variable;

Unfortunately my order variable is categorical now and my 11 sorts before 2;

How can I come across this situation???

Thanks

categorical

Apple

Baall

Cat

Dog

...

Zebra

data template_final;

set template_final;

order=put(categorical,$sort.);

run;

Re: Formats error

RichardinOz — Tue, 23 Oct 2012 23:58:29 GMT

You need to create an informat (specify invalue sort, no $).

Proc format ;

Invalue sort

'Apple' = 1

'Baall' = 2

'Cat' = 3

'Dog' = 4

;

data template_final;

set template_final;

order=input(categorical, sort.);

run;

The Put function always creates a character variable which is why '11' sorts before '2'

Regards

Richard in Oz

Re: Formats error

art297 — Tue, 23 Oct 2012 23:59:12 GMT

Karun,

It would be helpful to see your format. However, if you only assign a value and don't sort the file, why would you expect the records to change from their current position?

Re: Formats error

robertrao — Wed, 24 Oct 2012 00:06:46 GMT

I need to merge 4 datasets like this and some might be missing in a dataset and present in the other.......So this is the Template with which I can merge by order variable. Thats why I created this format/....

I take this format above and apply to those 4 datasetswhich have a similar template for which I wrote the format. The only thing would be that for example 39 is present in one .it may not in the other... so where it is not present we wilol not have 39 and we merge fimnally with order variable

Regards

Re: Formats error

robertrao — Wed, 24 Oct 2012 00:11:11 GMT

Hi,

I tried like you showed and it dint work out for me..

Thnaks for the help

Re: Formats error

art297 — Wed, 24 Oct 2012 00:12:29 GMT

I, for one, have no idea what you are trying to do. Can you provide some examples of what the four datasets look like and what you want to achieve?

Re: Formats error

robertrao — Wed, 24 Oct 2012 00:20:57 GMT

The 4 datasets have only 2 varibales each

categorical value

Under category we have

AGE

GENDER

BMI

DIABETES

and their corresponding value under the value variable

All have the samilar categorical values like under the format I created above.

The only difference lies:

if one dataset have Males the others does not have or vice versa...

Meaning in that study there are no males

So I created a template based on all the values and wrote a format. To that template I want to create order variable and same format is used to create order variables to all the 4 datasets...Then I ewant to merge by order variable!!!

Re: Formats error

robertrao — Wed, 24 Oct 2012 00:26:54 GMT

In other words the format was written for all unique values under categorical variable across the 4 different datasets.

If I dint do that then it does not merge properly...

Do you think I can Use the merge in a better way instead of this lengthy process???

Regards

Re: Formats error

Cynthia_sas — Wed, 24 Oct 2012 00:41:21 GMT

Hi:

Look on page 9 and page 17 of this paper: http://www2.sas.com/proceedings/forum2008/173-2008.pdf -- is this the kind of report you hope to create?

cynthia

Re: Formats error

robertrao — Wed, 24 Oct 2012 00:59:12 GMT

Hi,

Each of those 5 datasets have 2 variables in the end;

For example I am showing 2 datasets

categorical value categorical value

gender 55 gender 55

male 30 female 55

female 25

In the right side dataset there are no males.

When we try to merge these two datasets I guess we get some incorrect merging!!!

Re: Formats error

art297 — Wed, 24 Oct 2012 01:31:53 GMT

How are the datasets related, if they are, and what do you want the final output/datafile to look like?

Re: Formats error

robertrao — Wed, 24 Oct 2012 01:58:33 GMT

HI,

i want the final output to be like the shell I provided and they(datasets) are related by the categorical variable.as I said we have to deal wid categorical data as well as the sub categories present in one dataset and not present in the other

thnxxxx

Re: Formats error

art297 — Wed, 24 Oct 2012 02:39:56 GMT

Are you trying to do something like the following?:

data have1;

input categorical $ value;

cards;

gender 55

male 30

female 25

;

data have2;

input categorical $ value;

cards;

gender 55

female 55

;

data categories;

input categorical $;

cards;

gender

male

female

;

proc summary nway data=have1 classdata=categories order=data;

class categorical;

output out=new1(drop=_freq_ _type_) idgroup(out(value)=value1);

run;

proc summary nway data=have2 classdata=categories order=data;

class categorical;

output out=new2(drop=_freq_ _type_) idgroup(out(value)=value2);

run;

data want;

set new1;

set new2;

run;

Re: Formats error

Astounding — Wed, 24 Oct 2012 03:55:07 GMT

Create your format using leading zeros:

value $sort

'AGE'='01'

...

;

Re: Formats error

robertrao — Wed, 24 Oct 2012 03:57:10 GMT

Hi Sir,

Sorry for the delayed response.

It looks that you are very close to what I wanted. I tried to work this out and unfortunately it says:

even though I have a variable called categorical in all the 4 datasets

ERROR: Class variable categorical was not found in the preload data set or was incompatible with

the primary data set variable of the same name.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.STATQ_OUT1 may be incomplete. When this step was stopped there were 0

observations and 2 variable

Thanks

Re: Formats error

robertrao — Wed, 24 Oct 2012 04:37:12 GMT

Hi ,

I can also do

invalue sort <<<<<<<<no dollar and invalue

'AGE'=01 >>>>>>>>no quotes for 01

Regards

Re: Formats error

robertrao — Wed, 24 Oct 2012 05:10:34 GMT

Hi,

Thanks a lot for your time. I got the answer at last by doing what Astounding suggested.

Your code works fine for the example you gave me and it is what I am trying to acheive basically.

Based on a template I am trying to merge.

But when I tried to apply for my datasets it gives me an error.

template_final is like your vategories dataset and has categorical in chaaracter format.

I do not know where I am missing it

Regards

490 proc summary nway data=STATC_OUT classdata=template_final order=data;
491
492 class categorical;
493
494 output out=new1(drop=_freq_ _type_) idgroup(out(value)=value1);
495
496 run;

ERROR: Class variable categorical was not found in the preload data set or was incompatible with
the primary data set variable of the same name.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NEW1 may be incomplete. When this step was stopped there were 0
observations and 2 variables.

Re: Formats error

art297 — Wed, 24 Oct 2012 12:37:50 GMT

run proc contents on all of your files, including the new categorical one. There is apparently a difference between one or more of them.

Re: Formats error

robertrao — Wed, 24 Oct 2012 14:01:08 GMT

Hi,

Here are the attributes for the three datasets from the Proc Contents. Would you be able to tell me where the problem is????

This is like 'CATEGORIES dataset in your example....

*I got this by exporting one of the datasets which i am going to merge which has all combinations for my template into EXCEL, deleted the value column and imported back into SAS

Data Set Name WORK.TEMPLATE_FINAL Observations 55

Member Type DATA Variables 1

Engine V9 Indexes 0

Created Wed, Oct 24, 2012 08:36:24 AM Observation Length 25

Last Modified Wed, Oct 24, 2012 08:36:24 AM Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted NO

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

1 categorical Char 25 $25. $25. categorical

*DATASET1;

Data Set Name WORK.STATC_OUT Observations 55

Member Type DATA Variables 2

Engine V9 Indexes 0

Created Wed, Oct 24, 2012 08:36:01 AM Observation Length 400

Last Modified Wed, Oct 24, 2012 08:36:02 AM Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted NO

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

Alphabetic List of Variables and Attributes

# Variable Type Len Format

1 categorical Char 200 $200.
2 value Char 200 $200.

*DATASET2;

Data Set Name WORK.STATQ_OUT Observations 49

Member Type DATA Variables 2

Engine V9 Indexes 0

Created Wed, Oct 24, 2012 08:36:06 AM Observation Length 400

Last Modified Wed, Oct 24, 2012 08:36:06 AM Deleted Observations 0

Protection Compressed NO

Data Set Type Sorted NO

Label

Data Representation WINDOWS_32

Encoding wlatin1 Western (Windows)

Alphabetic List of Variables and Attributes

# Variable Type Len Format

1 categorical Char 200 $200.
2 value Char 200 $200.

Re: Formats error

art297 — Wed, 24 Oct 2012 14:17:20 GMT

I would change the length of categorical in WORK.TEMPLATE_FINAL to $200 so that it matches your other files. That COULD be the mismatch, but I don't have any way of testing it this morning.