Re: Assigning values based on values in other rows

Jasonnyc · Posted 04-21-2014 05:53 PM

Hi-

I'm trying to assign values based on values in other rows – having trouble with syntax. Below are two data sets - one is what I start with and the other is what I'm trying to accomplish.

Here is my criteria/logic:

I have to assign correct Placement_tactic to Adddnames.

If Placement_tactic is missing and AddType is =Default then:

1. I need to look at Placement_Type

2. Find the Placement_Type in other rows

3. If Placement_Tactic is populated

4. Then set placement_tactic to the same as Placement_Type

5. If AddType is not ‘Default’ leave alone – don’t change Placement_Tactic

Here is my code so far:

data want; set have;

retain _tactic;

by Placement_Type;

if FIRST. Placement_Type then _tactic=Placement_Tactic;

else Placement_Tactic=_tactic; run;

The issues that I'm having:

1. If AddType is not ‘Default’ then Placement_Tactic is be changed to FIRST. Placement.

2. It's possible that Placement_tactic can be missing and AddType ^= Default at the same time.

3. It's possible that there's no populated Placement_tactic for a certain Placement_type

4. There can be other AddTypes with missing Placement_tactic

5. Again only concerned coding AddTypes=default but FIRST.Placement may be an observation which is not AddTypes =default so the Placement_Tactic still needs to be based off the Placement_Type

6. In some occasions when AddType is =Default and Placement_Tactic has already been coded and does not need to be changed.

data have;

infile cards missover;

input AddNAme $16. AddType $9. Placement_Type $34. Placement_tactic $16. ;

cards;

160x600DefaultAd Default AOD_1H_BAU_ACQ_DMP_COM_RT_CPM

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM

300x600DefaultAd Default ROCKET_FUEL_1H

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard ROCKET_FUEL_1H PreRoll

;

This is what I'm trying to accomplish:

data WANT;

infile cards missover;

input AddNAme $16. AddType $9. Placement_Type $34. Placement_tactic $16. ;

cards;

160x600DefaultAd Default AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM Remarketing

300x600DefaultAd Default ROCKET_FUEL_1H PreRoll

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard ROCKET_FUEL_1H PreRoll

;

ShiroAmada · Posted 09-07-2017 03:26 AM

proc sql;
create table WANTas
select a.addname,a.addtype,a.placement_type,
b.placement_tactic
from HAVE (where=(placement_tactic is missing and addtype='Default')) a left join
HAVE (where=(placement_tactic is not missing and addtype ^='Default')) b
on a.placement_type=b.placement_type;

insert into WANT

select * from HAVE (where=(placement_tactic is not missing and addtype ^='Default'));
quit;

Assigning values based on values in other rows

Re: Assigning values based on values in other rows

Click image to register for webinar

Classroom Training Available!