I have a formula in Excel I want to replicate using SAS EG. For context, the term is 201801 = Spring 2018 and 201870 = Fall 2018. The CNUM (course number) column shows duplicated courses taught, blank values are for courses taught that need to be counted as well, but they are blank because they are not duplicated.
Task: Create a new column that flags 1 for all the blank values, flags 1 for the first course taught, and flags 0 for the duplicated courses considering the term.
Consider: CNUM can be the same value for different semesters and data sorted ascending.
Thanks in advance!
Data example:
| Term | CNUM | Desire New Variable |
|---|---|---|
| 201810 | 1 | |
| 201810 | 5P | 1 |
| 201810 | 5P | 0 |
| 201870 | 5P | 1 |
| 201870 | 5P | 0 |
| 201870 | 5X | 1 |
| 201910 | 5X | 1 |
| 201910 | 1 | |
| 201910 | AB | 1 |
| 201970 | AB | 1 |
| 201970 | AB | 0 |
| 202010 | 1 | |
| 202010 | XY | 1 |
| 202070 | XY | 1 |
| 202070 | XY | 0 |
| 202070 | XY | 0 |
Formula that solves my issue in excel:
=IF(B2="",1,IF(COUNTIFS($B$2:B2,B2,$A$2:A2,A2)>1,0,1))
B2= CNUM A2 = Term
I can't find the answer using SAS EG. HELP!
Hard to tell from your description but it sounds like you just want either of two different tests to be true. So use an OR operator.
data want;
set have;
by term cnum;
flag = missing(cnum) or first.cnum;
run;