topic Re: Manipulating column values in SAS in SAS Visual Analytics

Manipulating column values in SAS

DandarahZ — Thu, 10 Oct 2019 02:24:10 GMT

I have a category that has 2 values. I know that i am able to duplicate the category, however would like to know whether i am able to permanantly remove a value from the category.

Re: Manipulating column values in SAS

SASKiwi — Thu, 10 Oct 2019 05:58:56 GMT

What does "remove a value from a category" mean? Change a value in a column from non-missing to missing? Remove all rows that match a particular value? Or something else? Posting an example of what you want would be helpful.

Re: Manipulating column values in SAS

DandarahZ — Thu, 10 Oct 2019 06:07:57 GMT

Hello. I would like to apologize for being unclear with my message,

I have an occupancy column with 2 values, 0, 1. I would like to know if there is a way to separate the values into a column each eg: Unoccupied = 0

Occupied =1

I have tried using substrings, filters and creating new categories to manipulate the 2 values but to no avail. At the same time, a side question, is changing the datatype of category to measure possible?

Regards

Dan

Re: Manipulating column values in SAS

SASKiwi — Thu, 10 Oct 2019 06:16:40 GMT

Can you explain the type of report you are trying to produce? A screenshot of made-up data would be fine. The reason is to confirm if the way you want to change the data is the best for your report.

Re: Manipulating column values in SAS

DandarahZ — Thu, 10 Oct 2019 06:44:07 GMT

Hello, i thank you for the reply,

I am required to create some dashboards for the client. The dashboards will give an over view about room usage :

I inted to separate the occupance data into 2 different variables, occupied and non occupied. At the same time, i would like to find a way on how to convert the category datatype to measure to enable the be variables to be placed in the measure (y) axis of the graph above.

I apologize for any troubles caused

Regards

Dan

Re: Manipulating column values in SAS

FredrikE — Thu, 10 Oct 2019 09:52:21 GMT

Hi!

You can create two new calculated measures:

occupied:

=============

IF occupancy = '1' then return 1

ELSE 0

nonoccupied:

=============

IF occupancy = '0' then return 1

ELSE 0

Keep default aggregation (SUM).

//Fredrik

Re: Manipulating column values in SAS

DandarahZ — Fri, 11 Oct 2019 02:09:10 GMT

Hello and thank you for the reply,

I am unable to create calculated measures, the only option was for me to create a new aggregated measure. When brought to the formula screen, i dragged the boolean IF/ELSE statement to the workspace and dragged the category in. After it got dragged in, i got thrown an errror saying wrong type assigned.

These 2 screenshots are the options that are displayed, as i mentioned, there is no option to create aggregated measures

If I cant create calculated measures, what should be my next step? Do i have to ensure that the data thatt gets loaded into VA must be of the proper values? Does VA allow flexible type conversion?

Regards

Re: Manipulating column values in SAS

FredrikE — Fri, 11 Oct 2019 07:43:58 GMT

Hi!

Sorry if I mislead you, I only have the swedish version....:)

You should select "New Calculated Item..." and create two new "items", one for occupied and one for nonOccupied as a showed earlier.

Go into "text edit" (or what it's called in the english versoin....:)) and copy/paste the formulas I gave you, they might need some fine-tuning to fit your exact data 🙂

//Fredrik

Re: Manipulating column values in SAS

DandarahZ — Mon, 14 Oct 2019 08:31:49 GMT

Hello Mr Fredrik, thank you for your reply. I have a follow-up question. Can the same method be used if the data type is now a measure as compared to it previously being a category type?

Re: Manipulating column values in SAS

FredrikE — Mon, 14 Oct 2019 10:24:12 GMT

Hi!

Sorry, but I don't understand your question, can you add some example of what yo want to do?

🙂

//Fredrik

Re: Manipulating column values in SAS

DandarahZ — Tue, 15 Oct 2019 03:22:49 GMT

I apologize for the vagueness of the question.

As with the previous question, i want to separate the values (0,1) into 2 different categories. However, now instead of occupancy being a category type, it is now a measure type.

Will separating the values 0 and 1 be possible for occupancy with the data type of measure

Once again i apologize for any troubles caused.

Regards

Dan

Re: Manipulating column values in SAS

FredrikE — Tue, 15 Oct 2019 08:57:41 GMT

No probz 🙂

You can't create rows in VA, so if you have one row containing the measures you can't create two dimension values on separate rows, which i guess is what you want to do 🙂

You can create two (or more) dimension values on the same row, but I don't know if it is usable in your case...

//Fredrik

Re: Manipulating column values in SAS

DandarahZ — Wed, 16 Oct 2019 01:52:54 GMT

Hello Sir, i fail to understand what you meant in your past reply,

I hope the information i have provided can help you understand my situation better,

I intend to make a butterfly graph

As shown in the graph above, i require 1 category and 2 measure data types. I already have the category data prepared. I am however, having problems with the measure data. Within the occupancy measure data, there are 2 values as shown below. (Im am using category data for show/reference)

My question is that since occupancy is classified as a measure data type how do i separate the values 0,1 and the data into 2 different varibables. (below is a screen short of the intended outcome)

As mentioned not occupies will only have the zeros

and occupied will only have ones

I apologize for the repeated question Mr Fredrick. I appreciate any and all the help that you have given me

Regards

Dan

Re: Manipulating column values in SAS

FredrikE — Wed, 16 Oct 2019 11:53:11 GMT

Hi!

I think you can use my earlier suggestion, if I understand you correct 🙂

You can descide if you want to have 0/1 in both variables by changing the expression.

//Fredrik

"You can create two new calculated measures:

occupied:

=============

IF occupancy = '1' then return 1

ELSE 0

nonoccupied:

=============

IF occupancy = '0' then return 1

ELSE 0

Keep default aggregation (SUM)."