HOW TO WRITE A MACRO TO MAKE CODE EASIER

[Reader587]

Hello everyone, I want to know how to write a macro to make this code easier. Please and thank you. I have attached the code I have used below. It gets exhausting especially when the dataset is large. The data is from an excel worksheet but some of the rows are missing. 

For example we have a column named ID but only every 6 row got a name. 

data measurements2;
set measurements1;
if _N_ <=6 then ID="ITC2101-01";
if _N_ >=8 & _N_ <13 then ID="ITC2102-01";if _N_ ge 13 & _N_ < 19 then ID="ITC2103-01";
if _N_ ge 19 & _N_ lt 25 then ID="ITC2104-01";if _N_ ge 25 & _N_ lt 31 then ID="ITC2105-01";
if _N_ ge 31 & _N_ lt 37 then ID="ITC2106-01";if _N_ ge 37 & _N_ lt 43 then ID="ITC2107-01";
if _N_ ge 43 & _N_ lt 49 then ID="ITC2108-01";if _N_ ge 49 & _N_ lt 55 then ID="ITC2109-01";
if _N_ ge 55 & _N_ lt 61 then ID="ITC2110-01";if _N_ ge 61 & _N_ lt 67 then ID="ITC2111-01";
if _N_ ge 67 & _N_ lt 73 then ID="ITC2112-01";if _N_ ge 73 & _N_ lt 79 then ID="ITC2113-01";
if _N_ ge 79 & _N_ lt 85 then ID="ITC2114-01";if _N_ ge 85 & _N_ lt 91 then ID="ITC2115-01";
if _N_ ge 91 & _N_ lt 97 then ID="ITC2116-01";if _N_ ge 97 & _N_ lt 103 then ID="ITC2117-01";
if _N_ ge 103 & _N_ lt 109 then ID="ITC2118-01";if _N_ ge 109 & _N_ lt 115 then ID="ITC2119-01";
if _N_ ge 115 & _N_ lt 121 then ID="ITC2120-01";if _N_ ge 121 & _N_ lt 127 then ID="ITC2121-01";
if _N_ ge 127 & _N_ lt 133 then ID="ITC2122-01";if _N_ ge 133 & _N_ lt 139 then ID="ITC2123-01";
if _N_ ge 139 & _N_ lt 145 then ID="ITC2124-01";if _N_ ge 145 & _N_ lt 151 then ID="ITC2125-01";
if _N_ ge 151 & _N_ lt 157 then ID="ITC2126-01";if _N_ ge 157 & _N_ lt 163 then ID="ITC2127-01";
if _N_ ge 163 & _N_ lt 169 then ID="ITC2128-01";if _N_ ge 169 & _N_ lt 175 then ID="ITC2129-01";
if _N_ ge 175 & _N_ lt 181 then ID="ITC2130-01";
if _N_ ge 181 & _N_ lt 187 then ID="ITC2131-01";
if _N_ ge 187 & _N_ lt 193 then ID="ITC2132-01";
if _N_ ge 193 & _N_ lt 199 then ID="ITC2133-01";
if _N_ ge 199 & _N_ lt 205 then ID="ITC2134-01";
IF _n_ GE 205 & _n_ LT 211 THEN ID="ITC2135-01";
IF _n_ GE 211 & _n_ LT 217 THEN ID="ITC2136-01";
IF _n_ GE 217 & _n_ LT 223 THEN ID="ITC2137-01";
IF _n_ GE 223 & _n_ LT 229 THEN ID="ITC2138-01";
IF _n_ GE 229 & _n_ LT 235 THEN ID="ITC2139-01";
IF _n_ GE 235 & _n_ LT 241 THEN ID="ITC2140-01";
IF _n_ GE 241 & _n_ LT 247 THEN ID="ITC2141-01";
IF _n_ GE 247 & _n_ LT 253 THEN ID="ITC2142-01";
IF _n_ GE 253 & _n_ LT 259 THEN ID="ITC2143-01";
IF _n_ GE 259 & _n_ LT 265 THEN ID="ITC2144-01";
IF _n_ GE 265 & _n_ LT 271 THEN ID="ITC2145-01";
IF _n_ GE 271 & _n_ LT 277 THEN ID="ITC2146-01";
IF _n_ GE 277 THEN ID="ITC2147-01";
run;

[SASKiwi]

A lookup format is a much better way of doing this - no macro needed. Try this out:

data lookup;
  fmtname = 'Lookup';
  input @1 start @4 end @9 label $10.;
datalines;
 1   6 ITC2101-01
 8  12 ITC2102-01
13  18 ITC2103-01
19  24 ITC2104-01
;
run;

proc format cntlin = lookup;
run;

data measurements;
  do _n_ = 1 to 24;
    ID = put(_n_, lookup.);
    output;
    put _all_;
  end;
run;

[Tom]

The fact that the code is using _N_ (the automatic variable that is set to the data step iteration number) makes it look like you are trying to fill in missing cells caused by reading in a REPORT instead of reading in a DATASET.

Like a report like this:

proc report data=sashelp.class ;
  column sex age name ;
  define sex / order ;
run;

What does the Excel spreadsheet look like?   

Do the ID values actually appear on the sheet somewhere?

[Reader587]

My ID variable looks like your gender column but in an excel sheet.

[andreas_lds]

---

@Reader587 wrote:

My ID variable looks like your gender column but in an excel sheet.

---

Then a retained variable will solve the problem.

/* Prepare example data */
proc sort data= sashelp.class out= work.class;
   by Sex;
run;

data work.class;
   set work.class;
   by Sex;
   
   if not first.Sex then Sex = ' ';
run;

title 'Before';
proc print;run;


/* Fill the missing values */
data work.want;
   set work.class;
   
   length _Sex $ 1;
   retain _Sex;
   
   _Sex = coalescec(Sex, _Sex);
   Sex = _Sex;
   
   drop _Sex;
run;


title 'After';
proc print;run;

title;

[Kurt_Bremser]

Such code, which depends on the exact layout of a "dataset", is mostly useless. You have to rewrite it every time the layout changes.

Programming means making the computer do work for you. This code is the opposite.

From where do you infer the values and the locations (row numbers) to set them?

Also post an example of your existing dataset.

[Reader587]

ID

ITC2101-01

ITC2102-01

ITC2103

ITC2104-01

ITC2105

ITC2106-01

ITC2107-01

ITC2108-01

ITC2109-01

ITC2110-01

ITC2111-01

ITC2112-01

ITC2113-01

ITC2114-01

ITC2115-01

ITC2116

ITC2117-01

ITC2118-01

ITC2119

ITC2120

ITC2121-01

ITC2122-01

ITC2123-01

ITC2124-01

ITC2125-01

ITC2126-01

ITC2127-01

ITC2128-01

ITC2129

ITC2130-01

ITC2131-01

ITC2132-01

ITC2133-01

ITC2134

ITC2135-01

ITC2136-01

ITC2138

ITC2139

ITC2140

ITC2141-01

ITC2142

ITC2144-01

ITC2146-01

ITC2147-01

[Kurt_Bremser · Accepted solution]

Then this is a so-called LOCF (last observation carried forward) issue.

data want;
set have;
retain _id;
if id ne ""
then _id = id;
else id = _id;
drop _id;
run;

or

data want;
retain id;
set have (rename=(id=_id));
if _id ne "" then id = _id;
drop _id;
run;

Both codes untested, posted from my tablet.

[Reader587]

Thank You. I appreciate it.

[Tom]

So assuming the dataset you created from the Excel file is named HAVE and you want to create a dataset named WANT with the values of ID filled in you could do something like:

data want;
  set have ;
  if not missing(id) then _id=id;
  retain _id;
  id=_id;
  drop _id;
run;

This will create a new variable named _ID whose value will be retained from one iteration of the data step to the next.  When ID is populated the value is saved into the new _ID variable.  Creating _ID with a simple assignment statement will insure that it is defined as the TYPE and when character the same LENGTH as ID.

Then we save the retained value of _ID back into the original ID variable.  There is no need to keep the extra _ID variable.

[Reader587]

Thank You. I appreciate it.