topic Re: problem with the procedure in SAS Programming

problem with the procedure

makset — Fri, 08 Nov 2019 10:29:53 GMT

Hello
I have the following problem:
I transformed the test data set as follows:

data test0;
  infile datalines dlm='|' dsd truncover;
  length date var1 var2 8;
  informat date datetime20.;
  format date datetime20.;
  input date  var1 var2;
datalines;
18JAN2017:10:15:00|4.7333510969|2.1
18JAN2017:10:30:00|7.1205629521|2.2
18JAN2017:10:45:00|9.4999079941|2.3
18JAN2017:11:00:00|10.529906839|2.4
18JAN2017:11:15:00|8.2806338208|2.5
18JAN2017:11:30:00|4.8167229459|2.6
18JAN2017:11:45:00|1.9273408359|2.7
16FEB2017:00:15:00|6.7830217885|3.1
16FEB2017:00:30:00|9.1163551218|3.2
16FEB2017:00:45:00|12.752718758|3.3
16FEB2017:01:00:00|14.196902639|3.4
16FEB2017:01:15:00|13.654308753|3.5
16FEB2017:01:30:00|11.574892194|3.6
16FEB2017:01:45:00|4.7012445855|3.7
run;



data test1 (drop = var1 var2);
set test0 (rename = (date = date_7));

var1_1 = lag6(var1);
var1_2 = lag5(var1);
var1_3 = lag4(var1);
var1_4 = lag3(var1);
var1_5 = lag2(var1);
var1_6 = lag1(var1);
var1_7 = var1;
var2_1 = lag6(var2);
var2_2 = lag5(var2);
var2_3 = lag4(var2);
var2_4 = lag3(var2);
var2_5 = lag2(var2);
var2_6 = lag1(var2);
var2_7 = var2;

if mod(_n_, 7)  = 0;

run;

How to quickly and cleverly return to the initial state test1 to test0. If possible, I would also like to ask for a better proposal for the first procedure.

data test1 (drop = var1 var2);
set test0 (rename = (date = date_7));

var1_1 = lag6(var1);
var1_2 = lag5(var1);
var1_3 = lag4(var1);
var1_4 = lag3(var1);
var1_5 = lag2(var1);
var1_6 = lag1(var1);
var1_7 = var1;
var2_1 = lag6(var2);
var2_2 = lag5(var2);
var2_3 = lag4(var2);
var2_4 = lag3(var2);
var2_5 = lag2(var2);
var2_6 = lag1(var2);
var2_7 = var2;

if mod(_n_, 7)  = 0;

run;

Real data set have several thousand observations.
I made the transformation to save disk space and to speed up calculations, but at the end I have to return with the resulting data to the initial state.

Re: problem with the procedure

PaigeMiller — Fri, 08 Nov 2019 11:59:09 GMT

Please explain what you are trying to do in a lot more detail.

How to quickly and cleverly return to the initial state test1 to test0.

I don't understand what "return to the initial state" means.

Re: problem with the procedure

makset — Fri, 08 Nov 2019 12:32:19 GMT

test0

test1

from the test1 table the procedure (e.g. test2) to return to test 0

test2

Re: problem with the procedure

Reeza — Fri, 08 Nov 2019 18:22:03 GMT

Your data sets are not visible and if you want help with the code we'd have to type out your data. Make it easier for us to help you please.

Post data as text as minimum, preferably as a data step. If you don't know how to do that, here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Additionally, show what your inputs are, what the output is and describe the logic to get from one step to next. Please also ensure that your problem will scale, ie does it need to handle multiple groups or more variables?

@makset wrote:

test0

test1

from the test1 table the procedure (e.g. test2) to return to test 0

test2

Re: problem with the procedure

makset — Sun, 10 Nov 2019 15:37:59 GMT

I'm sorry I got mixed up.

Sometimes something that for us is not necessarily obvious to others.

The test0 table is the initial table at the input.

To save disk space and speed of calculations, I have made transpositions, but not classical ones, only every 7 observations.

Table test1 has changed as follows:
The date column has changed to date_7 but only the last observation in a given series, i.e. obs 7 and 14.

The column var1 row 1 to 7 changed to var1_1, var1_2, var1_3, var1_4, var1_5, var1_6, var1_7 row 1, and the column var2 row 8 to 14 changed to var2_1, var2_2, var2_3, var2_4, var2_5, var2_6, var2_7 row 2 .

My problem is that after making calculations from the test1 table, I have to go back to the form from the test0 table.

Re: problem with the procedure

Tom — Sun, 10 Nov 2019 16:55:02 GMT

You are TRANSPOSING your data. From TALL to WIDE and you now what to transpose it from WIDE to TALL.

You can use an ARRAY to help.

From TALL to WIDE.

data wide ;
  do i=1 to 14 ;
    set tall ;
    array var1_ var1_1-var1_7 var2_1-var2_7 ;
    var1_[i] = var1;
  end;
  drop i;
run;

From WIDE to TALL.

data tall;
  set wide;
  array var1_ var1_1-var1_7 var2_1-var2_7;
  do i=1 to dim(var1_);
    var1 = var1_[i];
    output;
  end;
  drop i var1_1-var1_7 var2_1-var2_7 ;
run;

Re: problem with the procedure

makset — Mon, 11 Nov 2019 09:10:44 GMT

Thank you for your answer.
The solution is not entirely correct but you have helped me a lot to solve my problem.

The solution should look like this:

data wide ;
  do i = 1 to 7;
    set tall ;
    array var1_ var1_1-var1_7;
	array var2_ var2_1-var2_7;
    var1_[i] = var1;
	var2_[i] = var2;
  end;
  drop i var1 var2;
run;

and:


data tall;
attrib date1 format = datetime21.;
  set wide;
  array var1_ var1_1-var1_7;
  array var2_ var2_1-var2_7;
  do i = 1 to dim(var1_);
    var1 = var1_[i];
	var2 = var2_[i];
	date1 = INTNX('minute',date, -15 * (7 - i));
    output;
  end;
  drop i var1_1-var1_7 var2_1-var2_7 date ;
  rename date1 = date;
run;

I have only one more question, you can solve this issue better and smarter:

date1 = INTNX('minute',date, -15 * (7 - i));

Thank you very much for your answer.

Re: problem with the procedure

Tom — Mon, 11 Nov 2019 13:40:20 GMT

First don't put datetime values into a variable named DATE. You will just confuse yourself no end.

A minute is 60 seconds. You are decrementing by 15 minutes for each iteration of the DO loop.

...
output;
datetime = datetime - '00:15't;
...

Re: problem with the procedure

makset — Mon, 11 Nov 2019 15:58:03 GMT

Your solution causes the datetime variable to decrease, i need it to grow

Re: problem with the procedure

Tom — Mon, 11 Nov 2019 16:03:54 GMT

So you want to add instead of subtract?

Which of the seven original datetime values did you keep when going from tall to wide?

If the last then loop from 7 to 1 instead of from 1 to 7 and subtract.

Or subtract 8*15 minutes before the DO loop and then add inside the DO loop.

datetime=datetime-8*'00:15't;
do i=1 to 7 ;
  datetime=datetime+'00:15't;
  ...
  output;
end;

Re: problem with the procedure

makset — Mon, 11 Nov 2019 16:20:54 GMT

ha ha! 🙂 nice tricks