blank cells

[maggieyao]

Hi,

 I am trying to convert the numerical column to string (monthyear_var=put(monthyear, 8.)), but find the first record is alway blank and the rest of the records just pushed down for one row and it is not aligned correctly with the original column. Below is what I got. Actually, even I simply copy the column by using the code:

 monthyear_var=monthyear;

 there is still a blank cell. Why does this happen? Thanks!

monthyear_var	monthyear
.	20150601
20150601	20150601
20150601	20150801
20150801	20150901
20150901	20150401
20150401	20150501
20150501	20150801
20150801	20150901
20150901	20150601
20150601	20151201
20151201	20150601
20150601	20150901
20150901	20150401
20150401	20150401
20150401	20150601
20150601	20150701
20150701	20150901
20150901	20151201
20151201	20150201
20150201	20150301
20150301	20150601
20150601	20150601
20150601	20150701
20150701	20151101

[ballardw]

Show your entire code and an example of the actual starting data would help.

If you get any messages in the log it would help to include those.

[maggieyao]

The codes are very straightforward, I use copy column as an example since it has exactly same issue as I was trying to convert from numerical to string. The data are read from SQL database with a libname of MMR.

data test;
month_var=month;
set MMR.mmr_2015;
run;

Below is the log:

1 The SAS System 15:26 Thursday, June 8, 2017

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='RX Monitoring Rpt';
4 %LET _CLIENTPROJECTPATH='U:\#Data_Active\RX Monitoring.egp';
5 %LET _CLIENTPROJECTNAME='RX Monitoring.egp';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 data test;
24 month_var=month;
25 set MMR.mmr_2015;
26 run;

NOTE: There were 360 observations read from the data set MMR.mmr_2015.
NOTE: The data set WORK.TEST has 360 observations and 13 variables.
NOTE: DATA statement used (Total process time):
real time 0.26 seconds
cpu time 0.01 seconds

27
28 GOPTIONS NOACCESSIBLE;
29 %LET _CLIENTTASKLABEL=;
30 %LET _CLIENTPROJECTPATH=;
31 %LET _CLIENTPROJECTNAME=;
32 %LET _SASPROGRAMFILE=;
33
34 ;*';*";*/;quit;run;
35 ODS _ALL_ CLOSE;
36
37
38 QUIT; RUN;
39

[Astounding · Accepted solution]

Your original statement was correct:

monthyear_var = put(monthyear, 8.);

However, the mistake you made was applying that statement before the SET statement has a chance to read in an observation.  Just put the SET statement before this statement and you should be all set.

[maggieyao]

Thank you so much Astounding! it worked! but how does that happen? is it because the source data info has not been read yet when SAS reads monthyear_var=put(monthyear, 8.), so it considers blank in the first record? Much appreciated!

[Astounding]

The SET statement is not just a label to identify the source of the data.  Instead, it is an instruction to read in the next observation.  The SET statement executes many times per DATA step, not just once.  Each time it executes it reads in another observation.

The statements within the DATA step execute in order.  So you need the SET statement to execute first, reading in an observation, and then the assignment statement for monthyear_var to execute next.  After outputting the results, the DATA step continues.  The SET statement reads in the next observation, the assignment statements computes another value, and the DATA step outputs the results.  Eventually, the SET statement runs out of observations to read.  When it fails to find another observation, the DATA step ends.