Thanks @Kurt_Bremser for your comments. I am able to run the program but getting where clause error. I checked the contents of my data to see the format and all the variables are numeric (see below image) but not sure why I am getting this where clause error. Please can you advise what should I need to change here?
options validvarname=v7;
proc import
datafile='H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\sample data file.xlsx'
dbms=xlsx
out=sample (
drop=MX MY MZ NA NB NC __ ___1
rename=(id_date=id)
where=(id ne '.')
)
replace
;
sheet="Sheet1";
run;
proc sort data=sample;
by id;
run;
Proc contents data=work.sample;
run;
proc transpose
data=sample
out=long (
drop=_name_
rename=(col1=y)
where=(y ne ' .')
)
;
by id;
var _:;
run;
data want;
set long;
date = input(_label_,5.) + '30dec1899'd;
format date yymmd7.;
drop _label_;
run;
Log
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 options validvarname=v7;
74 proc import
75 datafile='H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\sample data file.xlsx'
76 dbms=xlsx
77 out=sample (
78 drop=MX MY MZ NA NB NC __ ___1
79 rename=(id_date=id)
80 where=(id ne '.')
81 )
82 replace
83 ;
84 sheet="Sheet1";
85 run;
NOTE: Variable Name Change. 32874 -> _32874
NOTE: Variable Name Change. 32905 -> _32905
NOTE: Variable Name Change. 32933 -> _32933
NOTE: Variable Name Change. 32964 -> _32964
NOTE: Variable Name Change. 32994 -> _32994
NOTE: Variable Name Change. 33025 -> _33025
NOTE: Variable Name Change. 33055 -> _33055
NOTE: Variable Name Change. 33086 -> _33086
NOTE: Variable Name Change. 33117 -> _33117
NOTE: Variable Name Change. 33147 -> _33147
NOTE: Variable Name Change. 33178 -> _33178
NOTE: Variable Name Change. 33208 -> _33208
NOTE: Variable Name Change. 33239 -> _33239
NOTE: Variable Name Change. 33270 -> _33270
NOTE: Variable Name Change. 33298 -> _33298
NOTE: Variable Name Change. 33329 -> _33329
NOTE: Variable Name Change. 33359 -> _33359
NOTE: Variable Name Change. 33390 -> _33390
NOTE: Variable Name Change. 33420 -> _33420
NOTE: Variable Name Change. 33451 -> _33451
NOTE: Variable Name Change. 33482 -> _33482
NOTE: Variable Name Change. 33512 -> _33512
NOTE: Variable Name Change. 33543 -> _33543
NOTE: Variable Name Change. 33573 -> _33573
NOTE: Variable Name Change. 33604 -> _33604
NOTE: Variable Name Change. 33635 -> _33635
NOTE: Variable Name Change. 33664 -> _33664
NOTE: Variable Name Change. 33695 -> _33695
NOTE: Variable Name Change. 33725 -> _33725
NOTE: Variable Name Change. 33756 -> _33756
NOTE: Variable Name Change. 33786 -> _33786
NOTE: Variable Name Change. 33817 -> _33817
NOTE: Variable Name Change. 33848 -> _33848
NOTE: Variable Name Change. 33878 -> _33878
NOTE: Variable Name Change. 33909 -> _33909
NOTE: Variable Name Change. 33939 -> _33939
NOTE: Variable Name Change. 33970 -> _33970
NOTE: Variable Name Change. 34001 -> _34001
NOTE: Variable Name Change. 34029 -> _34029
NOTE: Variable Name Change. 34060 -> _34060
NOTE: Variable Name Change. 34090 -> _34090
NOTE: Variable Name Change. 34121 -> _34121
NOTE: Variable Name Change. 34151 -> _34151
NOTE: Variable Name Change. 34182 -> _34182
NOTE: Variable Name Change. 34213 -> _34213
NOTE: Variable Name Change. 34243 -> _34243
NOTE: Variable Name Change. 34274 -> _34274
NOTE: Variable Name Change. 34304 -> _34304
NOTE: Variable Name Change. 34335 -> _34335
NOTE: Variable Name Change. 34366 -> _34366
NOTE: Variable Name Change. 34394 -> _34394
NOTE: Variable Name Change. 34425 -> _34425
NOTE: Variable Name Change. 34455 -> _34455
NOTE: Variable Name Change. 34486 -> _34486
NOTE: Variable Name Change. 34516 -> _34516
NOTE: Variable Name Change. 34547 -> _34547
NOTE: Variable Name Change. 34578 -> _34578
NOTE: Variable Name Change. 34608 -> _34608
NOTE: Variable Name Change. 34639 -> _34639
NOTE: Variable Name Change. 34669 -> _34669
NOTE: Variable Name Change. 34700 -> _34700
NOTE: Variable Name Change. 34731 -> _34731
NOTE: Variable Name Change. 34759 -> _34759
NOTE: Variable Name Change. 34790 -> _34790
NOTE: Variable Name Change. 34820 -> _34820
NOTE: Variable Name Change. 34851 -> _34851
NOTE: Variable Name Change. 34881 -> _34881
NOTE: Variable Name Change. 34912 -> _34912
NOTE: Variable Name Change. 34943 -> _34943
NOTE: Variable Name Change. 34973 -> _34973
NOTE: Variable Name Change. 35004 -> _35004
NOTE: Variable Name Change. 35034 -> _35034
NOTE: Variable Name Change. 35065 -> _35065
NOTE: Variable Name Change. 35096 -> _35096
NOTE: Variable Name Change. 35125 -> _35125
NOTE: Variable Name Change. 35156 -> _35156
NOTE: Variable Name Change. 35186 -> _35186
NOTE: Variable Name Change. 35217 -> _35217
NOTE: Variable Name Change. 35247 -> _35247
NOTE: Variable Name Change. 35278 -> _35278
NOTE: Variable Name Change. 35309 -> _35309
NOTE: Variable Name Change. 35339 -> _35339
NOTE: Variable Name Change. 35370 -> _35370
NOTE: Variable Name Change. 35400 -> _35400
NOTE: Variable Name Change. 35431 -> _35431
NOTE: Variable Name Change. 35462 -> _35462
NOTE: Variable Name Change. 35490 -> _35490
NOTE: Variable Name Change. 35521 -> _35521
NOTE: Variable Name Change. 35551 -> _35551
NOTE: Variable Name Change. 35582 -> _35582
NOTE: Variable Name Change. 35612 -> _35612
NOTE: Variable Name Change. 35643 -> _35643
NOTE: Variable Name Change. 35674 -> _35674
NOTE: Variable Name Change. 35704 -> _35704
NOTE: Variable Name Change. 35735 -> _35735
NOTE: Variable Name Change. 35765 -> _35765
NOTE: Variable Name Change. 35796 -> _35796
NOTE: Variable Name Change. 35827 -> _35827
NOTE: Variable Name Change. 35855 -> _35855
NOTE: Variable Name Change. 35886 -> _35886
NOTE: Variable Name Change. 35916 -> _35916
NOTE: Variable Name Change. 35947 -> _35947
NOTE: Variable Name Change. 35977 -> _35977
NOTE: Variable Name Change. 36008 -> _36008
NOTE: Variable Name Change. 36039 -> _36039
NOTE: Variable Name Change. 36069 -> _36069
NOTE: Variable Name Change. 36100 -> _36100
NOTE: Variable Name Change. 36130 -> _36130
NOTE: Variable Name Change. 36161 -> _36161
NOTE: Variable Name Change. 36192 -> _36192
NOTE: Variable Name Change. 36220 -> _36220
NOTE: Variable Name Change. 36251 -> _36251
NOTE: Variable Name Change. 36281 -> _36281
NOTE: Variable Name Change. 36312 -> _36312
NOTE: Variable Name Change. 36342 -> _36342
NOTE: Variable Name Change. 36373 -> _36373
NOTE: Variable Name Change. 36404 -> _36404
NOTE: Variable Name Change. 36434 -> _36434
NOTE: Variable Name Change. 36465 -> _36465
NOTE: Variable Name Change. 36495 -> _36495
NOTE: Variable Name Change. 36526 -> _36526
NOTE: Variable Name Change. 36557 -> _36557
NOTE: Variable Name Change. 36586 -> _36586
NOTE: Variable Name Change. 36617 -> _36617
NOTE: Variable Name Change. 36647 -> _36647
NOTE: Variable Name Change. 36678 -> _36678
NOTE: Variable Name Change. 36708 -> _36708
NOTE: Variable Name Change. 36739 -> _36739
NOTE: Variable Name Change. 36770 -> _36770
NOTE: Variable Name Change. 36800 -> _36800
NOTE: Variable Name Change. 36831 -> _36831
NOTE: Variable Name Change. 36861 -> _36861
NOTE: Variable Name Change. 36892 -> _36892
NOTE: Variable Name Change. 36923 -> _36923
NOTE: Variable Name Change. 36951 -> _36951
NOTE: Variable Name Change. 36982 -> _36982
NOTE: Variable Name Change. 37012 -> _37012
NOTE: Variable Name Change. 37043 -> _37043
NOTE: Variable Name Change. 37073 -> _37073
NOTE: Variable Name Change. 37104 -> _37104
NOTE: Variable Name Change. 37135 -> _37135
NOTE: Variable Name Change. 37165 -> _37165
NOTE: Variable Name Change. 37196 -> _37196
NOTE: Variable Name Change. 37226 -> _37226
NOTE: Variable Name Change. 37257 -> _37257
NOTE: Variable Name Change. 37288 -> _37288
NOTE: Variable Name Change. 37316 -> _37316
NOTE: Variable Name Change. 37347 -> _37347
NOTE: Variable Name Change. 37377 -> _37377
NOTE: Variable Name Change. 37408 -> _37408
NOTE: Variable Name Change. 37438 -> _37438
NOTE: Variable Name Change. 37469 -> _37469
NOTE: Variable Name Change. 37500 -> _37500
NOTE: Variable Name Change. 37530 -> _37530
NOTE: Variable Name Change. 37561 -> _37561
NOTE: Variable Name Change. 37591 -> _37591
NOTE: Variable Name Change. 37622 -> _37622
NOTE: Variable Name Change. 37653 -> _37653
NOTE: Variable Name Change. 37681 -> _37681
NOTE: Variable Name Change. 37712 -> _37712
NOTE: Variable Name Change. 37742 -> _37742
NOTE: Variable Name Change. 37773 -> _37773
NOTE: Variable Name Change. 37803 -> _37803
NOTE: Variable Name Change. 37834 -> _37834
NOTE: Variable Name Change. 37865 -> _37865
NOTE: Variable Name Change. 37895 -> _37895
NOTE: Variable Name Change. 37926 -> _37926
NOTE: Variable Name Change. 37956 -> _37956
NOTE: Variable Name Change. 37987 -> _37987
NOTE: Variable Name Change. 38018 -> _38018
NOTE: Variable Name Change. 38047 -> _38047
NOTE: Variable Name Change. 38078 -> _38078
NOTE: Variable Name Change. 38108 -> _38108
NOTE: Variable Name Change. 38139 -> _38139
NOTE: Variable Name Change. 38169 -> _38169
NOTE: Variable Name Change. 38200 -> _38200
NOTE: Variable Name Change. 38231 -> _38231
NOTE: Variable Name Change. 38261 -> _38261
NOTE: Variable Name Change. 38292 -> _38292
NOTE: Variable Name Change. 38322 -> _38322
NOTE: Variable Name Change. 38353 -> _38353
NOTE: Variable Name Change. 38384 -> _38384
NOTE: Variable Name Change. 38412 -> _38412
NOTE: Variable Name Change. 38443 -> _38443
NOTE: Variable Name Change. 38473 -> _38473
NOTE: Variable Name Change. 38504 -> _38504
NOTE: Variable Name Change. 38534 -> _38534
NOTE: Variable Name Change. 38565 -> _38565
NOTE: Variable Name Change. 38596 -> _38596
NOTE: Variable Name Change. 38626 -> _38626
NOTE: Variable Name Change. 38657 -> _38657
NOTE: Variable Name Change. 38687 -> _38687
NOTE: Variable Name Change. 38718 -> _38718
NOTE: Variable Name Change. 38749 -> _38749
NOTE: Variable Name Change. 38777 -> _38777
NOTE: Variable Name Change. 38808 -> _38808
NOTE: Variable Name Change. 38838 -> _38838
NOTE: Variable Name Change. 38869 -> _38869
NOTE: Variable Name Change. 38899 -> _38899
NOTE: Variable Name Change. 38930 -> _38930
NOTE: Variable Name Change. 38961 -> _38961
NOTE: Variable Name Change. 38991 -> _38991
NOTE: Variable Name Change. 39022 -> _39022
NOTE: Variable Name Change. 39052 -> _39052
NOTE: Variable Name Change. 39083 -> _39083
NOTE: Variable Name Change. 39114 -> _39114
NOTE: Variable Name Change. 39142 -> _39142
NOTE: Variable Name Change. 39173 -> _39173
NOTE: Variable Name Change. 39203 -> _39203
NOTE: Variable Name Change. 39234 -> _39234
NOTE: Variable Name Change. 39264 -> _39264
NOTE: Variable Name Change. 39295 -> _39295
NOTE: Variable Name Change. 39326 -> _39326
NOTE: Variable Name Change. 39356 -> _39356
NOTE: Variable Name Change. 39387 -> _39387
NOTE: Variable Name Change. 39417 -> _39417
NOTE: Variable Name Change. 39448 -> _39448
NOTE: Variable Name Change. 39479 -> _39479
NOTE: Variable Name Change. 39508 -> _39508
NOTE: Variable Name Change. 39539 -> _39539
NOTE: Variable Name Change. 39569 -> _39569
NOTE: Variable Name Change. 39600 -> _39600
NOTE: Variable Name Change. 39630 -> _39630
NOTE: Variable Name Change. 39661 -> _39661
NOTE: Variable Name Change. 39692 -> _39692
NOTE: Variable Name Change. 39722 -> _39722
NOTE: Variable Name Change. 39753 -> _39753
NOTE: Variable Name Change. 39783 -> _39783
NOTE: Variable Name Change. 39814 -> _39814
NOTE: Variable Name Change. 39845 -> _39845
NOTE: Variable Name Change. 39873 -> _39873
NOTE: Variable Name Change. 39904 -> _39904
NOTE: Variable Name Change. 39934 -> _39934
NOTE: Variable Name Change. 39965 -> _39965
NOTE: Variable Name Change. 39995 -> _39995
NOTE: Variable Name Change. 40026 -> _40026
NOTE: Variable Name Change. 40057 -> _40057
NOTE: Variable Name Change. 40087 -> _40087
NOTE: Variable Name Change. 40118 -> _40118
NOTE: Variable Name Change. 40148 -> _40148
NOTE: Variable Name Change. 40179 -> _40179
NOTE: Variable Name Change. 40210 -> _40210
NOTE: Variable Name Change. 40238 -> _40238
NOTE: Variable Name Change. 40269 -> _40269
NOTE: Variable Name Change. 40299 -> _40299
NOTE: Variable Name Change. 40330 -> _40330
NOTE: Variable Name Change. 40360 -> _40360
NOTE: Variable Name Change. 40391 -> _40391
NOTE: Variable Name Change. 40422 -> _40422
NOTE: Variable Name Change. 40452 -> _40452
NOTE: Variable Name Change. 40483 -> _40483
NOTE: Variable Name Change. 40513 -> _40513
NOTE: Variable Name Change. 40544 -> _40544
NOTE: Variable Name Change. 40575 -> _40575
NOTE: Variable Name Change. 40603 -> _40603
NOTE: Variable Name Change. 40634 -> _40634
NOTE: Variable Name Change. 40664 -> _40664
NOTE: Variable Name Change. 40695 -> _40695
NOTE: Variable Name Change. 40725 -> _40725
NOTE: Variable Name Change. 40756 -> _40756
NOTE: Variable Name Change. 40787 -> _40787
NOTE: Variable Name Change. 40817 -> _40817
NOTE: Variable Name Change. 40848 -> _40848
NOTE: Variable Name Change. 40878 -> _40878
NOTE: Variable Name Change. 40909 -> _40909
NOTE: Variable Name Change. 40940 -> _40940
NOTE: Variable Name Change. 40969 -> _40969
NOTE: Variable Name Change. 41000 -> _41000
NOTE: Variable Name Change. 41030 -> _41030
NOTE: Variable Name Change. 41061 -> _41061
NOTE: Variable Name Change. 41091 -> _41091
NOTE: Variable Name Change. 41122 -> _41122
NOTE: Variable Name Change. 41153 -> _41153
NOTE: Variable Name Change. 41183 -> _41183
NOTE: Variable Name Change. 41214 -> _41214
NOTE: Variable Name Change. 41244 -> _41244
NOTE: Variable Name Change. 41275 -> _41275
NOTE: Variable Name Change. 41306 -> _41306
NOTE: Variable Name Change. 41334 -> _41334
NOTE: Variable Name Change. 41365 -> _41365
NOTE: Variable Name Change. 41395 -> _41395
NOTE: Variable Name Change. 41426 -> _41426
NOTE: Variable Name Change. 41456 -> _41456
NOTE: Variable Name Change. 41487 -> _41487
NOTE: Variable Name Change. 41518 -> _41518
NOTE: Variable Name Change. 41548 -> _41548
NOTE: Variable Name Change. 41579 -> _41579
NOTE: Variable Name Change. 41609 -> _41609
NOTE: Variable Name Change. 41640 -> _41640
NOTE: Variable Name Change. 41671 -> _41671
NOTE: Variable Name Change. 41699 -> _41699
NOTE: Variable Name Change. 41730 -> _41730
NOTE: Variable Name Change. 41760 -> _41760
NOTE: Variable Name Change. 41791 -> _41791
NOTE: Variable Name Change. 41821 -> _41821
NOTE: Variable Name Change. 41852 -> _41852
NOTE: Variable Name Change. 41883 -> _41883
NOTE: Variable Name Change. 41913 -> _41913
NOTE: Variable Name Change. 41944 -> _41944
NOTE: Variable Name Change. 41974 -> _41974
NOTE: Variable Name Change. 42005 -> _42005
NOTE: Variable Name Change. 42036 -> _42036
NOTE: Variable Name Change. 42064 -> _42064
NOTE: Variable Name Change. 42095 -> _42095
NOTE: Variable Name Change. 42125 -> _42125
NOTE: Variable Name Change. 42156 -> _42156
NOTE: Variable Name Change. 42186 -> _42186
NOTE: Variable Name Change. 42217 -> _42217
NOTE: Variable Name Change. 42248 -> _42248
NOTE: Variable Name Change. 42278 -> _42278
NOTE: Variable Name Change. 42309 -> _42309
NOTE: Variable Name Change. 42339 -> _42339
NOTE: Variable Name Change. 42370 -> _42370
NOTE: Variable Name Change. 42401 -> _42401
NOTE: Variable Name Change. 42430 -> _42430
NOTE: Variable Name Change. 42461 -> _42461
NOTE: Variable Name Change. 42491 -> _42491
NOTE: Variable Name Change. 42522 -> _42522
NOTE: Variable Name Change. 42552 -> _42552
NOTE: Variable Name Change. 42583 -> _42583
NOTE: Variable Name Change. 42614 -> _42614
NOTE: Variable Name Change. 42644 -> _42644
NOTE: Variable Name Change. 42675 -> _42675
NOTE: Variable Name Change. 42705 -> _42705
NOTE: Variable Name Change. 42736 -> _42736
NOTE: Variable Name Change. 42767 -> _42767
NOTE: Variable Name Change. 42795 -> _42795
NOTE: Variable Name Change. 42826 -> _42826
NOTE: Variable Name Change. 42856 -> _42856
NOTE: Variable Name Change. 42887 -> _42887
NOTE: Variable Name Change. 42917 -> _42917
NOTE: Variable Name Change. 42948 -> _42948
NOTE: Variable Name Change. 42979 -> _42979
NOTE: Variable Name Change. 43009 -> _43009
NOTE: Variable Name Change. 43040 -> _43040
NOTE: Variable Name Change. 43070 -> _43070
NOTE: Variable Name Change. 43101 -> _43101
NOTE: Variable Name Change. 43132 -> _43132
NOTE: Variable Name Change. 43160 -> _43160
NOTE: Variable Name Change. 43191 -> _43191
NOTE: Variable Name Change. 43221 -> _43221
NOTE: Variable Name Change. 43252 -> _43252
NOTE: Variable Name Change. 43282 -> _43282
NOTE: Variable Name Change. 43313 -> _43313
NOTE: Variable Name Change. 43344 -> _43344
NOTE: Variable Name Change. 43374 -> _43374
NOTE: Variable Name Change. 43405 -> _43405
NOTE: Variable Name Change. 43435 -> _43435
NOTE: Variable Name Change. 43466 -> _43466
NOTE: Variable Name Change. 43497 -> _43497
NOTE: Variable Name Change. 43525 -> _43525
NOTE: Variable Name Change. 43556 -> _43556
NOTE: Variable Name Change. 43586 -> _43586
NOTE: Variable Name Change. 43617 -> _43617
NOTE: Variable Name Change. 43647 -> _43647
NOTE: Variable Name Change. 43678 -> _43678
NOTE: Variable Name Change. 43709 -> _43709
NOTE: Variable Name Change. 43739 -> _43739
NOTE: Variable Name Change. 43770 -> _43770
NOTE: Variable Name Change. 43800 -> _43800
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
WARNING: The variable MX in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable MY in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable MZ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable NA in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable NB in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable NC in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable __ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable ___1 in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The import data set has 25 observations and 361 variables.
NOTE: WORK.SAMPLE data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.08 seconds
cpu time 0.07 seconds
86
87 proc sort data=sample;
88 by id;
89 run;
NOTE: There were 25 observations read from the data set WORK.SAMPLE.
NOTE: The data set WORK.SAMPLE has 25 observations and 361 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
90
91 Proc contents data=work.sample;
92 run;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.53 seconds
cpu time 0.50 seconds
93
94 proc transpose
95 data=sample
96 out=long (
97 drop=_name_
98 rename=(col1=y)
99 where=(y ne ' .')
100 )
101 ;
102 by id;
103 var _:;
104 run;
ERROR: WHERE clause operator requires compatible variables.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 25 observations read from the data set WORK.SAMPLE.
WARNING: The data set WORK.LONG may be incomplete. When this step was stopped there were 0
observations and 3 variables.
WARNING: Data set WORK.LONG was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
105
106 data want;
107 set long;
108 date = input(_label_,5.) + '30dec1899'd;
109 format date yymmd7.;
110 drop _label_;
111 run;
NOTE: There were 9000 observations read from the data set WORK.LONG.
NOTE: The data set WORK.WANT has 9000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
112
113 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
126
Regards,
Sara
... View more