BookmarkSubscribeRSS Feed
librasantosh
Obsidian | Level 7
  1. Dataset Update

     
    Given 2 data sets

    A
    memberid    membertype   programtype  firstname  lastname
    A001           type1                                  Eric          Smith
    A002           type1                                 John          Ed
    A003           type2              AA               Maria        Carr
    B001           type1              AA               Jack         Miller
    A005           type2                                 Maggie      Sliter
    A078           type1                                 Aan           Tailor
    B005           type2                                 Cliff            Honk


    B
    memberid     programtype      status     
    A001            BB                    active
    A002            unknown            active
    A078            AA                    active
    B004            AA                     inactive
    B005            BB                     active
    C004                                      active

    Request: 

    We need update the values of Programtype in Master Table A with the values of it in Table B. However, the requests are:
    1. Can not introduce new Observations, nor can all update, only conditions

Satisfy: when A.programtype is missing and B.programtype

Is not UNKNOWN, can update.data A;

input memberid $ membertype $ programtype $ firstname $ lastname $ ;

cards;

A001           type1               .                 Eric          Smith

A002           type1                .                 John          Ed

A003           type2              AA               Maria        Carr

B001           type1              AA               Jack         Miller

A005           type2               .                  Maggie      Sliter

A078           type1                .                 Aan           Tailor

B005           type2                .                 Cliff            Honk

;

run;

 

data B;

input memberid $ programtype $ status $;

cards;

A001            BB                    active

A002            unknown            active

A078            AA                    active

B004            AA                     inactive

B005            BB                     active

C004            .                          active

;

run;

 

4 REPLIES 4
andreas_lds
Jade | Level 19

"Can not introduce new Observations, nor can all update, only conditions"

Can you explain the highlighted part further? Maybe posting the expected result dataset will help.

 

 

Kurt_Bremser
Super User

If I read you right, this should work:

proc sort data=a;
by memberid;
run;

data want;
merge
  a (in=a)
  b (in=b keep=memberid programtype rename=(programtype=pt))
;
by memberid;
if a;
if b and missing(programtype) and pt ne 'unknown' then programtype = pt;
drop pt;
run;

proc print data=want noobs;
run;
librasantosh
Obsidian | Level 7
thanks a lot, i will try this
librasantosh
Obsidian | Level 7
  1. Dataset Update

     
    Given 2 data sets

    A
    memberid    membertype   programtype  firstname  lastname
    A001           type1                                  Eric          Smith
    A002           type1                                 John          Ed
    A003           type2              AA               Maria        Carr
    B001           type1              AA               Jack         Miller
    A005           type2                                 Maggie      Sliter
    A078           type1                                 Aan           Tailor
    B005           type2                                 Cliff            Honk


    B
    memberid     programtype      status     
    A001            BB                    active
    A002            unknown            active
    A078            AA                    active
    B004            AA                     inactive
    B005            BB                     active
    C004                                      active

    Request: 

    We need update the values of Programtype in Master Table A with the values of it in Table B. However, the requests are:
    1. Can not introduce new Observations, nor can all update, only conditions

Satisfy: when A.programtype is missing and B.programtype is not UNKNOWN, can update.

 

data A;

input memberid $ membertype $ programtype $ firstname $ lastname $ ;

cards;

A001           type1               .                 Eric          Smith

A002           type1                .                 John          Ed

A003           type2              AA               Maria        Carr

B001           type1              AA               Jack         Miller

A005           type2               .                  Maggie      Sliter

A078           type1                .                 Aan           Tailor

B005           type2                .                 Cliff            Honk

;

run;

 

data B;

input memberid $ programtype $ status $;

cards;

A001            BB                    active

A002            unknown            active

A078            AA                    active

B004            AA                     inactive

B005            BB                     active

C004            .                          active

;

run;

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1301 views
  • 0 likes
  • 3 in conversation