Help using Base SAS procedures

Help updating master dataset with new data

Reply
Occasional Contributor
Posts: 11

Help updating master dataset with new data

[ Edited ]
  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;

 

Super Contributor
Posts: 262

Re: help

"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.

 

 

Super User
Posts: 6,955

Re: help

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: help

thanks a lot, i will try this
Occasional Contributor
Posts: 11

Re: help

  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;

Ask a Question
Discussion stats
  • 4 replies
  • 150 views
  • 0 likes
  • 3 in conversation