topic Re: Data manipulation using proq sql in New SAS User

Data manipulation using proq sql

npr — Thu, 05 Dec 2019 12:28:23 GMT

I have the below table

Date	X	Y	Z
2001Q3	15248	2.8971	95.0332
2001Q4	15340	3.2731	97.4615
2002Q1	15430	3.8254	99.3809
2002Q2	15521	3.9667	100.9855
2002Q3	15613	4.0845	104.8513
2002Q4	15705	4.4269	107.0016
2003Q1	15795	3.3352	108.6860
2003Q2	15886	2.8685	110.5227
2003Q3	15978	3.4837	113.8978
2003Q4	16070	3.8459	115.8557
2004Q1	16161	4.1233	120.9971
2004Q2	16252	3.7870	122.2159
2004Q3	16344	3.5027	124.7153
2004Q4	16436	3.2087	128.2267

I calculate the std deviation of y and z till from 2001 q4 to 2003 q2 .

I have to create a new table which should look like below

Date	X	Y	Z
2001Q3	15248	2.8971	95.0332
2001Q4	15340	3.2731	97.4615
2002Q1	15430	3.8254	99.3809
2002Q2	15521	3.9667	100.9855
2002Q3	15613	4.0845	104.8513
2002Q4	15705	4.4269	107.0016
2003Q1	15795	3.3352	108.6860
2003Q2	15886	2.8685	110.5227
2003Q3	15978	3.4837 + Calculated std deviation of y	113.8978 - calculated std deviation of Z
2003Q4	16070	3.8458++ Calculated std deviation of y	115.8556 - calculated std deviation of Z
2004Q1	16161	4.1232+ Calculated std deviation of y	120.9971 - calculated std deviation of Z
2004Q2	16252	3.7869+ Calculated std deviation of y	122.2158 - calculated std deviation of Z
2004Q3	16344	3.5026+ Calculated std deviation of y	124.7152 - calculated std deviation of Z
2004Q4	16436	3.2086+ Calculated std deviation of y	128.2266 - calculated std deviation of Z

I tried using the case statement inside proc sql, but it did not work. Any assistance will be appreciated.

Re: Data manipulation using proq sql

PaigeMiller — Thu, 05 Dec 2019 12:51:15 GMT

@npr wrote:

I tried using the case statement inside proc sql, but it did not work. Any assistance will be appreciated.

Show us the code you tried. Explain what "did not work".

Re: Data manipulation using proq sql

Kurt_Bremser — Thu, 05 Dec 2019 12:54:30 GMT

Please post data in readily usable form, like

data have;
input date :yyq6. x y z;
format date yyq7.;
datalines;
2001Q3 15248 2.8971 95.0332
2001Q4 15340 3.2731 97.4615
2002Q1 15430 3.8254 99.3809
2002Q2 15521 3.9667 100.9855
2002Q3 15613 4.0845 104.8513
2002Q4 15705 4.4269 107.0016
2003Q1 15795 3.3352 108.6860
2003Q2 15886 2.8685 110.5227
2003Q3 15978 3.4837 113.8978
2003Q4 16070 3.8459 115.8557
2004Q1 16161 4.1233 120.9971
2004Q2 16252 3.7870 122.2159
2004Q3 16344 3.5027 124.7153
2004Q4 16436 3.2087 128.2267
;

and show the code you used for calculating the standard dev.

Re: Data manipulation using proq sql

npr — Thu, 05 Dec 2019 13:15:07 GMT

create table tablenew as
select
Date,
X,
case when Date >= "2003Q3" then (y+std(y)) else y end as y,
case when Date >= "2003Q3" then (z-std(z)) else z end as z
from have
;
quit;

Re: Data manipulation using proq sql

Kurt_Bremser — Thu, 05 Dec 2019 13:41:42 GMT

You need to restrict the time range for the calculation of std:

data have;
input date :yyq6. x y z;
format date yyq6.;
datalines;
2001Q3 15248 2.8971 95.0332
2001Q4 15340 3.2731 97.4615
2002Q1 15430 3.8254 99.3809
2002Q2 15521 3.9667 100.9855
2002Q3 15613 4.0845 104.8513
2002Q4 15705 4.4269 107.0016
2003Q1 15795 3.3352 108.6860
2003Q2 15886 2.8685 110.5227
2003Q3 15978 3.4837 113.8978
2003Q4 16070 3.8459 115.8557
2004Q1 16161 4.1233 120.9971
2004Q2 16252 3.7870 122.2159
2004Q3 16344 3.5027 124.7153
2004Q4 16436 3.2087 128.2267
;

proc sql;
create table tablenew as
select
  Date,
  X,
  case
    when Date >= input("2003Q3",yyq6.)
    then y + (select std(y) from have where date lt input("2003Q3",yyq6.))
    else y
  end as y,
  case
    when Date >= input("2003Q3",yyq6.)
    then z - (select std(z) from have where date lt input("2003Q3",yyq6.))
    else z
  end as z
from have
;
quit;

Re: Data manipulation using proq sql

npr — Thu, 05 Dec 2019 13:58:48 GMT

i only want the standard deviation of columns y and z , from 2001 q3 to 2003 q2

Re: Data manipulation using proq sql

Kurt_Bremser — Thu, 05 Dec 2019 14:07:25 GMT

@npr wrote:
i only want the standard deviation of columns y and z , from 2001 q3 to 2003 q2

That's what my code does. It adds/subtracts the result of summary function std for all periods before 2003q3 to all periods starting with 2003q3.