DATA Step, Macro, Functions and more

Formats error

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

Formats error

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;


Accepted Solutions
Solution
‎10-23-2012 10:39 PM
PROC Star
Posts: 7,360

Re: Formats error

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;

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Formats error

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

Super Contributor
Posts: 1,040

Re: Formats error

Hi,

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

Thnaks for the help

PROC Star
Posts: 7,360

Re: Formats error

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?

Super Contributor
Posts: 1,040

Re: Formats error

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

PROC Star
Posts: 7,360

Re: Formats error

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?

Super Contributor
Posts: 1,040

Re: Formats error

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!!!

Super Contributor
Posts: 1,040

Re: Formats error

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

SAS Super FREQ
Posts: 8,742

Re: Formats error


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

Super Contributor
Posts: 1,040

Re: Formats error

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!!!

PROC Star
Posts: 7,360

Re: Formats error

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

Super Contributor
Posts: 1,040

Re: Formats error

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

Solution
‎10-23-2012 10:39 PM
PROC Star
Posts: 7,360

Re: Formats error

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;

Super Contributor
Posts: 1,040

Re: Formats error

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

Super Contributor
Posts: 1,040

Re: Formats error

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 23 replies
  • 817 views
  • 6 likes
  • 5 in conversation