DATA Step, Macro, Functions and more

How to compare value in columns (same name, one with prefix) on the same row for multiple columns?

Reply
New Contributor
Posts: 2

How to compare value in columns (same name, one with prefix) on the same row for multiple columns?

Hi!

 

Quick question (with hopefully a quick answer).

 

I've a dataset with multiple rows. Each row does have - let's say - 41 columns of which column 1 is a unique identifier, the next 20 columns are customer characteristics and the next 20 columns are the same as the 20 columns before (same names but with a fixed prefix) and could contain - per column - a different value. 

 

I'm looking for a smart way to compare the value of each of the 20 columns with the value of the same column with the same name (but now with a prefix). In case the value is the same, nothing should happen. In case the value is different, the column should be updated with the value from the column with the same name but the fixed prefix.

 

I would like to prevent something as the below for each column:

 

if REGIO ne XXX_REGIO then REGIO = XXX_REGIO;
if GESLACHT ne XXX_GESLACHT then GESLACHT = XXX_GESLACHT;
if VOORLETTERS ne XXX_VOORLETTERS then VOORLETTERS  = XXX_VOORLETTERS

Example of the dataset with a limited number of columns:

 

ACCOUNTNUMBERREGIOGESLACHTVOORLETTERSXXX_REGIOXXX_GESLACHTXXX_VOORLETTERS
1A1R.H.M.B1R.H.L.
2A2D.A.A1D.A.
3B1A.A2A.
4A1S.B.M.J.A1S.B.M.K.
5A2L.W.B1L.W.

 

How could this be done?


Thanks in advance!


Regards,


Sven

Super User
Posts: 10,209

Re: How to compare value in columns (same name, one with prefix) on the same row for multiple column

There are several possible methods.

The first question: where do the prefixed columns come from? If you just built your table from two different tables and added the prefix on the way, you best drop that step and use update in a data step to combine your tables:

data class_upd;
set sashelp.class;
if name = 'Alfred' then age = 16;
run;

data class_updated;
update sashelp.class class_upd;
by name;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,599

Re: How to compare value in columns (same name, one with prefix) on the same row for multiple column

That's not a good way to model your data, instead of columns, move each block of 41 columns down to its own row.  This will make your life much easier in numerous ways.

 

As such with your structure now, your going to have to setup arrays for these blocks.  It depends on your column names for how easy this is, if you have:

ID  SET1_REGIO SET1_GESLACHT... SET2_REGIO...;

 

Then you can do something like:

data want;
  set have;
  array s1{20} set1:;
  array s2{20} set2:;
  ...
  do i=1 to 20;
    if s1{i}=s2{i}...
  end;
run;

That will work, however you will find its a lot of coding and effort.  Normalising - i.e. having rows rather than columns, will make this all far simpler.

Ask a Question
Discussion stats
  • 2 replies
  • 66 views
  • 0 likes
  • 3 in conversation