BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

23 REPLIES 23
RichardinOz
Quartz | Level 8

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

robertrao
Quartz | Level 8

Hi,

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

Thnaks for the help

art297
Opal | Level 21

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?

robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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?

robertrao
Quartz | Level 8

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

robertrao
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ


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

robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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

robertrao
Quartz | Level 8

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

art297
Opal | Level 21

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;

robertrao
Quartz | Level 8

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

robertrao
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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