After first SQL query, the macro variable is generated. After the second SQL query, no rows were seletced and hence the old macro variable is not revised by the query. The macro variable which is in memory somewhere is still intact.   After ending the session and re-running your second query has no rows selected and hence the Macrovariable is not created. You want print it to see. There is no such variable in memory and that is the reason for getting the WARNING.   Hope this is useful to you. ... View more

I am datasp.   Whom do you ask this question? Before asking question, write the name of person who replied.     I pointed out much earlier in my post that you are comparing a substring of 3 characters with the value of either '09' or 'E8'. This comparison is wrong. Again I asked you not to use SUBSTR() function and showed it.   What you mean by "The value should be three but I typed wrong"?   Are you prepared to list your input variables once? ... View more

Here are two ways . One is CALL SCAN() , another is prxchange(). Data ds; Infile datalines; Input idno name&$30. Team $ strtwght endwght ; call scan(name,1,p1,l1); call scan(name,-1,p2,l2); Middle_Name1=strip(substr(name,l1+1,p2-l1-1)); Middle_Name2=prxchange('s/^\w+|\w+$//',-1,strip(name)); drop p1 p2 l1 l2; Datalines; 1331 Jason Schock paul Long blue 187 172 1067 Kanoko john rav Nagasaka green 135 122 1251 Richard roy granny Rose blue 181 166 1192 Charlene tin Armstrong yellow 152 139 1352 Bette Schock green 156 137 1262 Yao Chen Garg blue 196 180 1124 Adrienne Fink green 156 142 ; run; ... View more

  data shift; set score; array apps[*] time: makeup; do i=1 to dim(apps)-1; * i goes 1 short if dim(apps); if apps[i]=. then do; * last i can be 1 less than dim(apps) ; * there is no use to shift the Missing value at the dim(apps) position; do j=i to dim(apps)-1; * to use j + 1, j must be less than dim(apps); apps[j]=apps[j+1]; * j cann't be dim(app) here; end; mu='-'||compress(i); end; end; drop i j; run;     ... View more

@toconero wrote: Can you explain Ballardw how it run? is it possible to make two steps?. Thank you If the base data set already exists then replace the informat and input with a SET statement referencing the existing data and remove the datalines block.   The code is basically looking at two values, for example the first and second. If they are the same we add to a counter of "sameness", if they differ than the existing count is the value to increment in the Month variable (a lousy name, RunLength would be more indicative of what it contains). After updating that we need to reset the count to one as we have started a new sequence. ... View more

LAG does exactly what you want.  You just have your data sorted incorrectly. Also remember to call LAG() on every observation. proc sort data=have; by id yr; run; data want ; set have ; by id yr ; lag_no=lag(no); if not first.id then l_no=lag_no; run; ... View more

You seemed to have not gone through my answers. Just see the end of my answer.  ... View more

@Cynthia_sas,   Thank you so much for your warm approach .Your approach and your reccommendation are much appreciated.    At this moment, learning Hash is also far from me, it seems difficult but I can guess how powerful Hash code is . I need a time to converge it.   About your second reccomandation you are totally right. This statement is what I'm facing nowadays -> that generally is a sign that the requirements are still in flux and that you are going to be constantly fiddling with/modifying your program to suit the data they send you.   Thank you ... View more

Here is an IML solution (it uses elementwise multiplication).     data dataset1; input var1 var2 var3; datalines; .5 .2 .1 .1 .2 0 .2 .5 .3 ; data dataset2; input var1 var2 var3; datalines; 1 0 1 1 1 0 0 1 1 ; proc iml; use dataset1; read all into ds1; n = nrow(ds1); use dataset2; read all into ds2; elementProd = ds1#ds2; want = elementProd[,+] / n ; print want; quit;     ... View more

It is IML thing: data DataA; input v1 v2 v3 v4; cards; 1 2 3 4 2 3 4 1 6 7 2 4 ; run; proc iml; use DataA; read all var _num_ into x[c=vnames]; close; col=vnames[x[,<:>]]; create max from col; append from col; close; quit; data want; merge DataA Max; run; ... View more