topic Re: Char to numeric and aggregate in SAS Programming

Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 06:24:50 GMT

I'm trying to find the maximum value from VALUE field group by on two fields (Id, CRF) as shown below. I'd would like to understand whether this program is logically correct. I'm not getting the desired results with this program. I don't wish to create a any new field after converting the VALUE from character to numeric.

Input Data:

Id	        CRF	         Value
PLAP24MRT_MV	[INS]#MAX1	-956461.285475
PLAP24MRT_MV	[INS]#MAX2	-30254.271916

Desired Result:

Id	        CRF	 Value
PLAP24MRT_MV	[INS]	-30254.271916

Program:

proc Sql;
    create table Want as select distinct * from (
        select Id
               ,scan(CRF,1,'#') as CRF, (
            case 
                when scan(CRF,-1,,'ka')="MIN" then strip(put(min(input(Value,24.6)),24.6))
                when scan(CRF,-1,,'ka')="MAX" then strip(put(max(input(Value,24.6)),24.6))
                when scan(CRF,-1,,'ka')="AVG" then strip(put(avg(input(Value,24.6)),24.6))
                when scan(CRF,-1,,'ka')="STD" then strip(put(std(input(Value,24.6)),24.6))
                else ''
            end )
        as Value
        from INPUT
        group by Id,CRF);
quit;

Re: Char to numeric and aggregate

ballardw — Tue, 13 Jul 2021 06:35:41 GMT

Make a small set of data where you know what the result should be. You should include some of those CRF values with "min","avg" and "std" to properly test your code.

Run your code.

Does it match your expectation?.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 06:43:08 GMT

@ballardw Tried already. It's not producing the desired results. May I know whether the charcter to numeric and then to character conversion is correct in CASE WHEN as shown in initial post?

Re: Char to numeric and aggregate

ballardw — Tue, 13 Jul 2021 07:10:04 GMT

I don't use subqueries in Case statements but to select a maximum value by group I would expect to see something like

Proc sql;
   create table want as
   select <group vars>, max(numeric expression)
   from dataset
   group by <group vars>
   ;
quit;

If you only want to consider values where CRF contains MAX that would be a WHERE subsetting the data set such as:

Proc sql;
   create table want as
   select <group vars>, Put(max(numeric expression),24.6) as value
   from (select * from  dataset where scan(CRF,-1,,'ka')='MAX'  )
   group by <group vars>
   ;
quit;

Or similar.
You want to test what your INPUT is creating. You may find that if you have integers the value is not as expected.

Consider:

data example; 
   x="-956461";
   y=input(x,24.6);
run;

Look very closely for a decimal in the above. Leading spaces might make things even more entertaining. Input unless you are dealing with fixed implicit decimal places is typically not desired. An F24. informat (or 24.0) will read the numeric values just fine.

Of course one does wonder why a value like character to begin with.

As always, actual example data in the form of a data step might get something that works quicker.

Since your "example" of two lines had none of "avg" "min" or "std" it is real hard for us to test any code.

Re: Char to numeric and aggregate

tarheel13 — Tue, 13 Jul 2021 07:13:26 GMT

proc sql;
	create table have 
	(id char(200),CRF char(200), value char(200));
	insert into have 
	values('PLAP24MRT_MV','[INS]#MAX1','-956461.285475')
	values('PLAP24MRT_MV', '[INS]#MAX2','-30254.271916');	
quit;

data have2;
	set have;
	new_CRF=scan(CRF,1,'#');
	val=input(value,24.6);
run;

proc sql;
	create table have3 as
	select distinct id, new_CRF,
	case when scan(CRF,-1,,'ka')="MIN" then strip(put(min(val),26.4))
    when scan(CRF,-1,,'ka')="MAX" then strip(put(max(val),26.4))
    when scan(CRF,-1,,'ka')="AVG" then strip(put(mean(val),26.4))
    when scan(CRF,-1,,'ka')="STD" then strip(put(std(val),26.4))
    else '' end as value
    	from have2
    	group by id, new_CRF;
quit;

I don't see the issue? This gets -30254.271916 as the result.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 07:18:21 GMT

@tarheel13 can we do this in two steps by removing have2 datastep? Also may I know why you're changing the format to 26.4? Can't we retain with 24.6?

Re: Char to numeric and aggregate

tarheel13 — Tue, 13 Jul 2021 07:24:13 GMT

The 24.6 was a mistake. But anyway I agree with @ballardw and I don't think you need to use subqueries either. You can put it in one SQL and remove the DATA step. The result is still the same.

proc sql;
	create table have3 as
	select distinct id, scan(CRF,1,'#') as CRF,
	case when scan(CRF,-1,,'ka')="MIN" then strip(put(min(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="MAX" then strip(put(max(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="AVG" then strip(put(mean(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="STD" then strip(put(std(input(value,26.4)),26.4))
    else '' end as value
    	from have2
    	group by id, CRF;
quit;

Re: Char to numeric and aggregate

Patrick — Tue, 13 Jul 2021 07:35:45 GMT

@David_Billa Getting sample data in the form of a data step with at least two cases wouldn't have hurt.

Does below give you what you're after? I was actually surprised that the functions really only execute on aggregate level during group by processing and not just on row level - but it appears to work.

data have;
  input (Id CRF) (:$20.) Value :$20.;
  datalines;
PLAP24MRT_MV [INS]#MAX1 -956461.285475
PLAP24MRT_MV [INS]#MAX2 -30254.271916
PLAP24MRT_MV [INS]#MIN1 -956461.285475
PLAP24MRT_MV [INS]#MIN2 -30254.271916
;

proc Sql;
  create view v_inter as
    select 
      id,
      scan(CRF,1,'#') as CRF length=10,
      scan(CRF,-1,,'ka') as function length=3,
      input(Value,32.) as value format=24.6
    from have
    ; 
  create table Want as 
    select distinct
      Id,
      CRF, 
      function,
      case function 
        when "MIN" then min(Value)
        when "MAX" then max(Value)
        when "AVG" then avg(Value)
        when "STD" then std(Value)
        else missing(value)
      end 
      as Value format=24.6
    from v_inter
    group by Id,CRF,Function
  ;
quit;

Also important:

Do NOT use an informat with a decimal portion unless you know exactly what you're doing.

IF your source string doesn't have a decimal portion then SAS will assume the should be one and divide the number by 10 power decimal portion.

data demo;
  input Value 24.6;
  format value 24.6;
  datalines;
-956461.285475
-956461.
-956461
;

Re: Char to numeric and aggregate

tarheel13 — Tue, 13 Jul 2021 07:29:46 GMT

Also don't think the group by is necessary but again you have not posted your data so it is hard to tell you.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 07:32:27 GMT

@Patrick thank you. I want the solution in one step instead of two steps. Also I want the VALUE field to be character in the result.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 07:37:54 GMT

@tarheel13 It's not producing the desired results. You can execute and check

proc sql;
create table have 
	(id char(200),CRF char(200), value char(200));
	insert into have 
	values('PLAP24MRT_MV','[INS]#MAX1','-956461.285475')
	values('PLAP24MRT_MV', '[INS]#MAX2','-30254.271916');	
quit;


proc sql;
	create table have3 as
	select distinct id, scan(CRF,1,'#') as CRF,
	case when scan(CRF,-1,,'ka')="MIN" then strip(put(min(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="MAX" then strip(put(max(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="AVG" then strip(put(mean(input(value,26.4)),26.4))
    when scan(CRF,-1,,'ka')="STD" then strip(put(std(input(value,26.4)),26.4))
    else '' end as value
    	from have
    	group by id, CRF;
quit;

Re: Char to numeric and aggregate

tarheel13 — Tue, 13 Jul 2021 07:39:58 GMT

I also told you to remove the group by. Or else do what @Patrick did and include the function in the group by clause.

Re: Char to numeric and aggregate

Patrick — Tue, 13 Jul 2021 07:44:32 GMT

@David_Billa Below how you get value as a string in the result.

Using a view which only executes when called in the 2nd SQL allows imho for code that's easier to read and maintain. From a processing perspective things only execute in the 2nd SQL.

It wouldn't be that hard to move all the logic from the view into the 2nd SQL - but I'm not going to do this as it's just ugly without adding benefit.

proc Sql;
  create view v_inter as
    select 
      id,
      scan(CRF,1,'#') as CRF length=10,
      scan(CRF,-1,,'ka') as function length=3,
      input(Value,32.) as value format=24.6
    from have
    ; 
  create table Want as 
    select distinct
      Id,
      CRF, 
      function,
      put(
          case function 
            when "MIN" then min(Value)
            when "MAX" then max(Value)
            when "AVG" then avg(Value)
            when "STD" then std(Value)
            else missing(value)
          end 
          , 24.6 -l)
        as Value 
    from v_inter
    group by Id,CRF,Function
  ;
quit;

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 07:45:50 GMT

@tarheel13 data which you have in your program is correct. Without group by we won't be getting the desired results.

Re: Char to numeric and aggregate

tarheel13 — Tue, 13 Jul 2021 07:47:30 GMT

Okay, I am leaving this conversation. The group by also does not get what you want.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 07:56:32 GMT

@Patrick Please clarify why you've added function in your program.

Re: Char to numeric and aggregate

Patrick — Tue, 13 Jul 2021 08:02:14 GMT

@David_Billa Because based on the sample data and code you've posted I've made the assumption that for the same ID there could be data as I've created in my post. So for same ID a value for CRF that could always contain a sub-string [INS] but then different function names in the remaining string. If so then you would likely want to aggregate on the lowest level of the function given the code you shared.

Re: Char to numeric and aggregate

David_Billa — Tue, 13 Jul 2021 08:05:20 GMT

One last question. Can't we achieve it without creating function? Also what is the meaning of -l in PUT function?

Re: Char to numeric and aggregate

Patrick — Tue, 13 Jul 2021 08:17:25 GMT

@David_Billa The -l stands for LeftAligned. Without it the string in value would be right aligned with leading blanks which is something people normally don't want.

Yes. with SAS SQL you could also not use variable Function in the group by clause and it would still work. SAS somehow internally still creates groups per aggregate function. But then: The aggregation is on function level so this is just about clean code that someone else can understand and maintain (and you still can understand in one year's time).

Re: Char to numeric and aggregate

ballardw — Tue, 13 Jul 2021 15:25:35 GMT

@David_Billa wrote:

One last question. Can't we achieve it without creating function? Also what is the meaning of -l in PUT function?

The -L (to make it more legible) in Put means to left justify the result. By default Put with a numeric format will right justify the value and if the value does not "fill" the format there will be leading zeroes.

Example:

data example;
    x=3;
    y= quote(put(x,f12.2));
    z= quote(put(x,f12.2 -L));
run;

The Quote places quote marks around the value so you can see just how the value has resolved with the Put function.

The presence of leading spaces can lead to all sorts of interesting behaviors depending what you do with a value like that later. Some are apparent "duplicate" values that are treated as different, failure to convert back to numeric with INPUT, unexpected spaces in concatenation operations and unexpected "sort" order results.