topic Re: Indexing a column + lagged variables in SAS Programming

Indexing a column + lagged variables

UniversitySas — Wed, 19 Sep 2018 09:00:03 GMT

Hi,

I have two questions, which I hope can be highlighted using the image below:

So
1) Is there a way to index columns? For instance, if the n'th value of column Var = "Unrestricted contribution" then the [n-7]th value of "Var_Name" = "ABC", or [n+2]th value of "Var_Name" = "ABC".

If not, can this be done using Lagged variables?

I tried the following:

DATA Sample_Clean;
	SET Sample_Orig;
	Var_Name7 = lag7(Var);
	IF Var = "unrestricted contribution" or Var = "Unrestricted Contribution" THEN Var_Name7 = lag7(VAR) ; 
	ELSE Var_Name7 = .;
RUN;

But this just returns <.> for my entire column. I get that I probably need to create a loop somehow, but I am unsure!

Any help is really appreciated.

Thanks

Re: Indexing a column + lagged variables

RW9 — Wed, 19 Sep 2018 09:40:56 GMT

Please review the guidance on how to post a question, found under the post button. Provide test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Show what the output from this test data should be, and explain the process. I am afraid the picture you present doesn't seem to match any of the logic, that just shows an if statement.

In terms of lag(), lag7() means 7 observations prior to the current observation, which for _n_ <= 6 is missing. Also varname7 doesn't appeara in your screenshot, or I presume your data, therefore it will default to being created as numeric, and nothing character can be assigned, this is why you see a list of "."'s which are missing numeric. See above, post test data and what the output should look like from that test data, you are getting yourself confused in a lot of different things here.

Re: Indexing a column + lagged variables

FreelanceReinh — Wed, 19 Sep 2018 14:30:16 GMT

Hi @UniversitySas,

Yes, you can create an index for a column (variable), but in your case it seems that the "key" is just the observation number. So, you could use the POINT= option of the SET statement for the look ahead (+7 observations) and the LAG2 function for the look back (-2 observations).

Example:

data Sample_Clean(drop=c var7);
retain c 'unrestricted contribution';
set Sample_Orig nobs=n;
p=_n_+7;
if p<=n then set Sample_Orig(keep=var rename=(var=var7)) point=p;
else var7=' ';
if lowcase(lag2(var))=c | lowcase(var7)=c then var_name='ABC';
run;

Re: Indexing a column + lagged variables

UniversitySas — Thu, 20 Sep 2018 02:38:43 GMT

Thanks for your reply; this is what I was looking for.

Although, would you be able to direct me to any resources which guide through indexing a column specifically? The reason is because I plan to do this with other observations in my "Var" Column, which have a less certain criteria (e.g., contains >= 4 integers...)

All the indexing and array examples I found seem to be to create more columns or cycle through the columns, instead of within a column.

Re: Indexing a column + lagged variables

FreelanceReinh — Thu, 20 Sep 2018 08:34:44 GMT

I think a good starting place would be Understanding SAS Indexes. With an index for a variable X (or a combination of variables) you could (among other things) use the KEY= option of the SET statement in place of the POINT= option in my example, provided that values of X characterize the target observations.

Another powerful and flexible (though complex) tool for non-sequential retrieval and modification of data is the hash object (see Using the Hash Object). For example, you could load dataset Sample_Orig into a hash object (which is held in memory), then jump to certain observations (characterized by values of the "key items" of the hash object), modify values of VAR_NAME or other variables ("data items" of the hash object) in these observations and finally write the contents of the hash object to dataset Sample_Clean.