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
;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.