DATA Step, Macro, Functions and more

Assigning values based on values in other rows

Reply
New Contributor
Posts: 3

Assigning values based on values in other rows

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 AOD_1H_BAU_ACQ_DMP_COM_RT_CPM 

160x600DefaultAd Default AOD_1H_BAU_ACQ_DMP_COM_RT_CPM 

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM 

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM 

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM 

300x600DefaultAd Default ROCKET_FUEL_1H 

300x600DefaultAd Default ROCKET_FUEL_1H 

300x600DefaultAd Default ROCKET_FUEL_1H 

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard ROCKET_FUEL_1H PreRoll

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 AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition 

160x600DefaultAd Default AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie 

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie 

160x600DefaultAd Default AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie 

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM Remarketing 

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM Remarketing

300x600DefaultAd Default AOD_1H_BAU_ACQ_MLP_CPM Remarketing

300x600DefaultAd Default ROCKET_FUEL_1H PreRoll

300x600DefaultAd Default ROCKET_FUEL_1H PreRoll

300x600DefaultAd Default ROCKET_FUEL_1H PreRoll

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

RedAdd100 standard AOD_1H_BAU_ACQ_DMP_COM_RT_CPM Acquisition

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

BlueAdd100 standard AD.COM_1H_BAU_ACQ_C2C_BAU_DISPLAY Moblie

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard AOD_1H_BAU_ACQ_MLP_CPM Remarketing

SaleAd standard ROCKET_FUEL_1H PreRoll

SaleAd standard ROCKET_FUEL_1H PreRoll

;

Ask a Question
Discussion stats
  • 0 replies
  • 161 views
  • 0 likes
  • 1 in conversation