BookmarkSubscribeRSS Feed
lo88lo
Fluorite | Level 6

Hi,

I have a problem with the assigment to a value.

Thank you so much!

 

 

TABLE A:  Categorical Variable + Cumulative (from 0 to 1)

 

VAR_A_1     VAR_A_2

A                    0,5

B                    0,7

C                    0,9

D                    1

 

TABLE B: Categorical variable + Random Variable (from 0 to 1)

 

VAR_B_1     VAR_B_2

X1                 0,43

X2                 0,76

X1                 0,98

X1                 0,23

 

 

 

TABLE C: Result

from table B : Categorial Variable, Random Variable,

from Table A: Categorical variable that corresponds to the upper of the class within which is the value of VAR_B_2

VAR_B_1     VAR_B_2           VAR_A_1     VAR_A_2      

X1                 0,43                    A                        0,5 

X2                 0,76                    C                        0,9

X1                 0,98                    D                          1

X1                 0,23                    A                        0,5   

 

I tried with 2 Macros but is very slow because Table A and Table B are very long.

I thought to use formats but do not know how.

 

Thank you

 

8 REPLIES 8
Kurt_Bremser
Super User

So, table A gives you ranges. How many observations (records) does A have?

The most elegant solution would be to dynamically create a value format with ranges from A and apply that to B.

lo88lo
Fluorite | Level 6

Thank you so much

Table A 10.000 rows

Table B 13.000 rows

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post your test data as a datastep so we don't have to type it in and guess what your data looks like.  Follow this topic if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

It looks like you are merging table A onto table B, based on smallest VAR_A_2 which is greater than VAR_B_2.  The question is, what if you have multiple rows which have the same value?  So for row one you take A as 0.43 < 0.5 but what if B was 0.5.

 

Something like this will work, but not if you have multiples:

proc sql;
  select B.*,
         A.*
  from   TABLE_B B
  left join (select * 
               from (select * 
                       from TABLE_A 
                       where VAR_A_2 > B.VAR_VAR_B_2)
               group by VAR_A_2
               having VAR_A_2=min(VAR_A_2)) A
  on 1=1;
quit; 

Untested!!

Shmuel
Garnet | Level 18

You can use formats as:

 

proc format lib=work;

     value vara1f

    0 - 0.5    = "A"

    0.5 - 0.7 = "B"

    0.7 - 0.9 = "C"

    0.9 - 1.0 = "D"

    ;

     value vara2f

    0 - 0.5    = 0.5

    0.5 - 0.7 = 0.7

    0.7 - 0.9 = 0.9

    0.9 - 1.0 = 1

    ;

; run;

 

data C;

  set B;

        var_A_1 = put(var_B_2, vara1f.);

         var_A_2 = put(var_B_2,vara2f.);

run;

lo88lo
Fluorite | Level 6

Thank you so much. Is not possible the same value for different row in Table A

 

user24feb
Barite | Level 11

Data A;

Length Var_A_1 $2.;

Input VAR_A_1 $ VAR_A_2;

Datalines;

A 0.5

B 0.7

C 0.9

D 1

;

Data B;

Length Var_B_1 $2.;

Input VAR_B_1 $ VAR_B_2;

Datalines;

X1 0.43

X2 0.76

X1 0.98

X1 0.23

;

Data D (Drop=rc);

Length Var_A_1 $2. Var_A_2 8.;

If _N_ eq 1 Then Do;

Declare Hash H (Dataset:'A',Ordered:'y');

Declare HIter HI ('H');

H.Definekey('Var_A_2');

H.Definedata(All:'y');

H.Definedone();

Call Missing (Var_A_1,Var_A_2); *!;

End;

Set B;

rc=HI.First();

Do While (not rc);

If Var_A_2 gt Var_B_2 Then Leave;

rc=HI.Next();

End;

Run;

lo88lo
Fluorite | Level 6

I try the solutions!

 

thank you all

Ksharp
Super User
Data A;
  Length Var_A_1 $2.;
  Input VAR_A_1 $ VAR_A_2;
  Datalines;
A 0.5
B 0.7
C 0.9
D 1
;

Data B;
  Length Var_B_1 $2.;
  Input VAR_B_1 $ VAR_B_2;
  Datalines;
X1 0.43
X2 0.76
X1 0.98
X1 0.23
;
proc sql;
select *
 from a,b
  where  VAR_A_2 gt  VAR_B_2
   group by b. VAR_B_1,b.VAR_B_2
    having  VAR_A_2- VAR_B_2=min( VAR_A_2- VAR_B_2);
quit;

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
  • 8 replies
  • 1311 views
  • 2 likes
  • 6 in conversation