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

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.

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
  • 4 replies
  • 805 views
  • 0 likes
  • 3 in conversation