Transferring "T" to missing values and then to "0"

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 112
Accepted Solution

Transferring "T" to missing values and then to "0"

Hello everyone,

 

I put a part of data set as below:

RochesterMonthlySnowfall,,,,,,,,,,,
,,,,,,,,,,,
Season,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Total,
,,,,,,,,,,,
1884-85,0,T,1,27.1,22.2,17,3.5,19.5,T,90.3,
1885-86,0,1.7,8.2,8.4,16.9,16,6.5,7,0,64.7,
1886-87,0,T,22.2,12.5,12,18.4,6.3,1.2,0,72.6,
1887-88,0,0.2,2.2,9.3,21.3,4.1,13.2,0.4,0,50.7,
1888-89,0,T,4,15.5,17.8,22,17.5,5.4,0,82.2,
1889-90,0,T,5.7,6.1,20.2,14.8,19,T,0,65.8,
1890-91,0,0,2.1,29.2,16.1,24.6,12.2,0.3,0.1,84.6,
1891-92,0,0.1,9.7,4.7,26.4,10.3,25.1,0.8,T,77.1,
1892-93,0,T,14,19.2,15.9,29.8,8.1,9.6,0,96.6,
1893-94,0,0.5,6.1,27.6,20,29.5,5.4,13.3,0,102.4,
1894-95,0,T,11.1,22.1,26.5,23.6,9.5,0.6,0,93.4,
1895-96,0,1.5,5.9,8.7,22.5,39.1,45.1,1,0,123.8,
1896-97,0,T,5.5,13.9,20.1,13.7,8.1,5.2,0,66.5,
1897-98,0,0,10.1,18.4,32.1,26.8,1.2,2.4,0,91,
1898-99,0,T,10.6,27,16.6,16.3,21.2,4.3,T,96,
1899-00,T,T,1.3,21.5,24.7,28.5,54,1.3,0,131.3,
1900-01,0,0,17,20.3,29.8,36.9,13.7,23.8,T,141.5,
1901-02,0,0.1,14.1,14.5,23.8,23,1.2,2.3,T,79,
1902-03,0,0.1,4.1,27.7,18.1,15.6,2.4,0.3,0,68.3,
1903-04,0,0.6,4.4,16.1,27.2,17.2,10.7,19.5,T,95.7,
1904-05,0,0.2,2.1,15.8,27.5,15.2,7,0.5,0,68.3,

And the question was:

Write a SAS program that reads in the data for snowfall from the 1884-85 season until the 2001-02 season (the 2002-03 season is incomplete so please eliminate it from consideration). Use PROC PRINT to print out a listing of the data with an appropriate title, and put season as the first column of o/p—do not show line numbers.

    1. If you read the data as numeric (probably best since you might want to do math on it if you decide to calculate summary statistics later), then the T’s will be converted to missing values and create a warning in the SAS LOG.
    2. Because T really means about 0 snow, please convert all the missing values to 0’s
  1. My code is as below, and my result is as attached.
    %let dirdata=/folders/myfolders;
    libname Week_5 "&dirdata";
    run;
    
    options errors=200;
    data snowfall;
    
    infile "&dirdata/Week_5/RochesterSnowfall.csv" dlm=',' dsd firstobs=5 obs=122;
    length season $7;
    input season Sep Oct Nov Dec Jan Feb Mar Apr May Total;
    array Month (10) Sep--Total;
    do i=1 to 10 ;
    if Month(i)=. then Month(i)=0;
    end;
    myTot= sum(of Sep--May);
    diff=round(myTol-Total,3);
    if diff ne 0 then put "**Error" myTot= Total= diff=;
    run;
    proc print data=snowfall;
    run;
    
    
    I have two questions: firstly, I did not get error message reading in "T". Was this because I used "Length Season $7" so I already defined season as a character value? If it was the case, how did SAS know I want "T" to be "0"?
  2. From my result, because I intended to keep "MyTot" "i" and "diff" in my result. I want to ask that, why did the "i" remained "11"?since I got 10 obs in "i". In addition, what does the last column "mytol" means? Was that "Mytotal"? I did not see that column in my code above.

Thank you very much for reading. Hope there would be someone interested in this question Smiley Happy


Accepted Solutions
Solution
‎03-10-2018 09:22 PM
Respected Advisor
Posts: 3,018

Re: Transferring "T" to missing values and then to "0"

[ Edited ]

Oct is declared as a numeric variable in your INPUT statement. When it tried to read the letter T, it can't read it as a number, an so it is set to missing. Then your DO loop turns missings into zero.

 

The variable I is incremented from one to 10, then it is set to 11 but the loop knows not to use it at 11 since you said the loop only goes to 10. There's really no reason to keep variable I in your data set. MYTOL is not defined. You typed MYTOL (probably instead of MYTOT) so this becomes a variable in your data set.

--
Paige Miller

View solution in original post


All Replies
Solution
‎03-10-2018 09:22 PM
Respected Advisor
Posts: 3,018

Re: Transferring "T" to missing values and then to "0"

[ Edited ]

Oct is declared as a numeric variable in your INPUT statement. When it tried to read the letter T, it can't read it as a number, an so it is set to missing. Then your DO loop turns missings into zero.

 

The variable I is incremented from one to 10, then it is set to 11 but the loop knows not to use it at 11 since you said the loop only goes to 10. There's really no reason to keep variable I in your data set. MYTOL is not defined. You typed MYTOL (probably instead of MYTOT) so this becomes a variable in your data set.

--
Paige Miller
Frequent Contributor
Posts: 112

Re: Transferring "T" to missing values and then to "0"

Posted in reply to PaigeMiller

whoo~Very clear!

Thank you very much!!

I understand how it ran like this now.

Respected Advisor
Posts: 4,736

Re: Transferring "T" to missing values and then to "0"

@jc3992

Below a way for dealing with the 'T's and to convert them directly to 0 without getting warnings or errors in the log:

proc format;
  invalue myBest (default=32)
    'T' = 0
    other=[best32.]
    ;
run;

data have;
  infile datalines dlm=',' dsd truncover;
  input Season $ (Sep Oct Nov Dec Jan Feb Mar Apr May Total) (:myBest.);
  datalines;
1884-85,0,T,1,27.1,22.2,17,3.5,19.5,T,90.3,
1885-86,0,1.7,8.2,8.4,16.9,16,6.5,7,0,64.7,
;
run;
Frequent Contributor
Posts: 112

Re: Transferring "T" to missing values and then to "0"

Thank you very much!

I learned many commands I have not yet learned before.Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 117 views
  • 2 likes
  • 3 in conversation