BookmarkSubscribeRSS Feed
niloo
Obsidian | Level 7

Hi,

I am new to sas and I am trying to calculate the return of some companies using this command: ret=dif(RI)/lag(RI);

I followed these steps to import my csv table to sas. I have 4 variables: 1)company name:comnam  2)Datastream code: dscd (this code is a mix of numbers and letters) 3) date :caldt        4) Return Index: RI

proc import out=ARI

datafile= "c:\evantus\A-RI-main.csv" dbms = csv replace;

getnames=yes;

datarow= 2;

GUESSINGROWS=20000;

format caldt date9.;

run;

proc sort data=ari;

by  caldt dscd;

run;

data reta;

set ari;

by caldt;

ret=dif(RI)/lag(RI);

if first. dscd then ret=.;

keep comnam dscd caldt ri;

run;

I have imported the data but when I run the last part of these statements the result does not show the "ret".

I used the same formula to calculate the return of mu index yesterday and it worked. The only difference between my Index file and my company file is that my index file only had 2 variables: caldt and RI.

What I used for my index was:

data MSCI;

set index;

by caldt;

ewretd=dif(RI)/lag(RI);

keep caldt ewretd;

run;

Could you please help me solve this issue?

Thanks,

Niloo

16 REPLIES 16
Steelers_In_DC
Barite | Level 11

Give an example of your input and what output you are getting.

niloo
Obsidian | Level 7

Please find attached the input and output files.

The A-RI-main file is the input :

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86

and rita.csv is my out output

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86
NIPPON TELG. & TEL. - TOT RETURN IND740847########157.86

Thank you very much for the help

Steelers_In_DC
Barite | Level 11

Based on your input and code it should work.  I feel you are leaving out some steps.

Input:

data index;

infile cards;

input caldt$ ri;

cards;

######## 157.86

######## 157.86

;

data MSCI;

set index;

by caldt;

ewretd=dif(RI)/lag(RI);

keep caldt ewretd;

run;

output:

Obscaldt ewretd
1########  .
2########  0

Your output doesn't look accurate, you can't have anything but caldt and ewretd in your output because of the keep statement.

niloo
Obsidian | Level 7

what steps?

Steelers_In_DC
Barite | Level 11

I don't know what you aren't showing me.  What I'm saying is with the statement "keep caldt ewretd;" you wouldn't get the output you are showing me.  You would get the output I pasted.  You can't have any other variable except caldt and ewretd with that keep statement.

niloo
Obsidian | Level 7

sorry the previous input and outputs were for my index.

I tried but I could not attach the files so I copy pasted the first few lines:

input:

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/1999158.78
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999149.65
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999154.21
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/1999159.69
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/1999159.69



output

comnamdscdcaldtRI
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/1999158.78
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999149.65
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/1999153.3
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/1999155.13
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999154.21
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/1999157.86
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/1999159.69
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/1999159.69

Thanks

Nilo

niloo
Obsidian | Level 7

As you see the input and out put are exactly the same. the return is not calculated Smiley Sad

Reeza
Super User

Post a proc contents output from your input data set:

proc contents data=MSCI;

run;

niloo
Obsidian | Level 7

By input do you mean the ari dataset that I entered to sas?

I did it:

The SAS System

The CONTENTS Procedure

     
Data Set NameWORK.ARIObservations28769
Member TypeDATAVariables4
EngineV9Indexes0
Created07/31/2015 12:07:01Observation Length88
Last Modified07/31/2015 12:07:01Deleted Observations0
Protection CompressedNO
Data Set Type SortedYES
Label
Data RepresentationWINDOWS_64
Encodingwlatin1 Western (Windows)

   
Data Set Page Size65536
Number of Data Set Pages39
First Data Page1
Max Obs per Page743
Obs in First Data Page727
Number of Data Set Repairs0
ExtendObsCounterYES
FilenameC:\Users\esy\AppData\Local\Temp\SAS Temporary Files\_TD8272_ESY-THINK_\ari.sas7bdat
Release Created9.0401M1
Host CreatedX64_7PRO

        
4RINum8BEST12.BEST32.
3caldtNum8MMDDYY10.MMDDYY10.
1comnamChar64$64.$64.
2dscdChar6$6.$6.

   
Sortedbycaldt dscd
ValidatedYES
Character SetANSI

ballardw
Super User

Can't be the "output".

This code:

data reta;

set ari;

by caldt;

ret=dif(RI)/lag(RI);

if first. dscd then ret=.;

keep comnam dscd caldt ri;

run;

will not execute for two reasons: 1) you cannot use First. notation with a variable that doesn't appear on a BY statement 2) there is a space between First. and the variable DSCD. You either ran different code or did not show the code you actually ran.

Steelers_In_DC
Barite | Level 11

Ballardw;

There's another piece of code that has a keep statement that only has 2 variables, the output has 4, so that also cannot be the code that goes along with that output.

niloo
Obsidian | Level 7

Dear Ballardw,

I am copy pasting from my Editor!Smiley Sad

proc import out=ARI

datafile= "c:\evantus\A-RI-main.csv" dbms = csv replace;

getnames=yes;

datarow= 2;

GUESSINGROWS=20000;

format caldt date9.;

run;

proc sort data=ari;

by  caldt dscd;

run;

data reta;

set ari;

by caldt dscd;

ret=dif(RI)/lag(RI);

if first.dscd then ret=.;

keep comnam dscd caldt ri;

run;

Even after deleting the space between  first. and dscd it does not work.

DSCD is on the by statement.

Thanks,

NilooSmiley Sad

Reeza
Super User

add ret to your keep statement.

niloo
Obsidian | Level 7

Hi Reeza,

Thank you so much for your answer. I added ret and deleted RI from the keep statement. a column called ret was added to sas results. however, all the values of the column were "."

So I deleted the if statement: if first.dscd then ret=.;

After this I got results using only the following the statements:

data reta;

set ari;

by caldt dscd;

ret=dif(RI)/lag(RI);

keep comnam dscd caldt ret;

run;

I am not sure if my results are correct or not now that I deleted the if statement. Can you tell me what is wrong with my if statement?

This is how the result looks like now:(it looks perfect but some of the rets in later rows are too big and I guess maybe it is because of deleting the if statement???)

comnamdscdcaldtret
NIPPON TELG. & TEL. - TOT RETURN IND74084712/10/1999
NIPPON TELG. & TEL. - TOT RETURN IND74084712/13/19990
NIPPON TELG. & TEL. - TOT RETURN IND74084712/14/19990.005828
NIPPON TELG. & TEL. - TOT RETURN IND74084712/15/1999-0.00579
NIPPON TELG. & TEL. - TOT RETURN IND74084712/16/1999-0.02889
NIPPON TELG. & TEL. - TOT RETURN IND74084712/17/19990.011937
NIPPON TELG. & TEL. - TOT RETURN IND74084712/20/1999-0.0118
NIPPON TELG. & TEL. - TOT RETURN IND74084712/21/1999-0.02381
NIPPON TELG. & TEL. - TOT RETURN IND74084712/22/19990.02439
NIPPON TELG. & TEL. - TOT RETURN IND74084712/23/19990
NIPPON TELG. & TEL. - TOT RETURN IND74084712/24/19990.011937
NIPPON TELG. & TEL. - TOT RETURN IND74084712/27/1999-0.00593
NIPPON TELG. & TEL. - TOT RETURN IND74084712/28/19990.023669
NIPPON TELG. & TEL. - TOT RETURN IND74084712/29/19990.011593
NIPPON TELG. & TEL. - TOT RETURN IND74084712/30/19990
LIXIL GROUP - TOT RETURN IND70226312/31/1999-0.49033
NIPPON TELG. & TEL. - TOT RETURN IND74084712/31/19990.962035
LIXIL GROUP - TOT RETURN IND7022631/3/2000-0.49033

Thanks,

Niloo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1487 views
  • 3 likes
  • 5 in conversation