Prev: MDB files compare
Next: converting character datetime(ISO) to numeric datetime20.
From: hewei2004 on 23 Mar 2010 18:52
Suppose I have a file like this:
car.csv

BRAND ADVERTISE SALE1998Q4 SALE1999Q1 SALE1999Q2 SALE1999Q3
SALE1999Q4
TOYOTA 1999/07/01 200 450
300 500 650
FORD 1999/01/01 751 654
873 654 541

How do I create a variable called 1QAFTER 1QBEFORE, that contains the
sale data 1 quarter before and 1 quarter after the advertise date

So the desired output is :
BRAND ADVERTISE SALE1998Q4 SALE1999Q1 SALE1999Q2 SALE1999Q3
SALE1999Q4 1QAFTER 1QBEFORE
TOYOTA 1999/07/01 200 450
300 500 650 650
300
FORD 1999/01/01 751 654
873 654 541 873 751

Many thanks
From: Reeza on 24 Mar 2010 19:12
On Mar 23, 3:52 pm, hewei2004 <hewei2...(a)gmail.com> wrote:
> Suppose I have a file like this:
> car.csv
>
> BRAND    ADVERTISE   SALE1998Q4 SALE1999Q1 SALE1999Q2 SALE1999Q3
> SALE1999Q4
> TOYOTA    1999/07/01          200               450
> 300                500              650
> FORD        1999/01/01          751               654
> 873               654              541
>
> How do I create a variable called 1QAFTER 1QBEFORE, that contains the
> sale data 1 quarter before and 1 quarter after the advertise date
>
> So the desired output is :
> BRAND    ADVERTISE   SALE1998Q4 SALE1999Q1 SALE1999Q2 SALE1999Q3
> SALE1999Q4 1QAFTER 1QBEFORE
> TOYOTA    1999/07/01          200               450
> 300                500              650               650
> 300
> FORD        1999/01/01          751               654
> 873               654              541                873        751
>
> Many thanks

If this is all the fields you're analyzing I'd probably hard code it.

ie if advertise ge 1999/0101 and advertise lt 19990401 then do
q1before=sale1998q4; q1after=sale1999q2 etc
Code not tested (but 1QAfter is not a valid sas name)

If you're doing more quarters and advertising periods looking into
formatting the advertise column as the year quarter format and then
consider macro variables to dynamically choose the columns.

HTH,
Reese
 | 
Pages: 1
Prev: MDB files compare
Next: converting character datetime(ISO) to numeric datetime20.