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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.