I have a data set with a lot of variables with the year and month as variable name like 2014-01,2019-01,2019-02,2019-05,2020-01,2020-03,2021-01,2021-02,2021-03, 2021-04, 2021-05. I would like to sum the total of 2014-2020. I tried code below but get the error The sum functio call does not have enough argumensts. What do i do wrong?
Eerder_2021 = (Sum(of _201:)+sum(of _2020:));
Hello @rgjpot,
You will need to use the notation of SAS name literals:
Eerder_2021 = sum(of '201'n:, of '2020'n:);
Show us the proc contents of your table please so we can see the variable names.
Dear Cris,
Thanks for the reply. As requested the proc contents
The CONTENTS Procedure
GD.GLIMS_DATA_2021_MERGE | 30169 |
DATA | 27 |
V9 | 0 |
16-07-2021 09:54:27 | 264 |
16-07-2021 09:54:27 | 0 |
NO | |
NO | |
WINDOWS_64 | |
wlatin1 Western (Windows) |
65536 |
122 |
1 |
248 |
232 |
0 |
YES |
H:\Mijn Documenten\Finacien\mi\Mi 2021\Glims\glims_data_2021_merge.sas7bdat |
9.0401M6 |
X64_7PRO |
DS\rpot |
8MB |
8060928 |
2019-01 | Num | 8 | BEST17. | 2019-01 | |
2019-02 | Num | 8 | BEST3. | 2019-02 | |
2019-03 | Num | 8 | BEST3. | 2019-03 | |
2019-04 | Num | 8 | BEST3. | 2019-04 | |
2019-05 | Num | 8 | BEST3. | 2019-05 | |
2019-08 | Num | 8 | BEST3. | 2019-08 | |
2019-10 | Num | 8 | BEST3. | 2019-10 | |
2019-11 | Num | 8 | BEST3. | 2019-11 | |
2020-01 | Num | 8 | BEST3. | 2020-01 | |
2020-02 | Num | 8 | BEST3. | 2020-02 | |
2020-03 | Num | 8 | BEST3. | 2020-03 | |
2020-05 | Num | 8 | BEST3. | 2020-05 | |
2020-06 | Num | 8 | BEST3. | 2020-06 | |
2020-07 | Num | 8 | BEST3. | 2020-07 | |
2020-08 | Num | 8 | BEST3. | 2020-08 | |
2020-09 | Num | 8 | BEST3. | 2020-09 | |
2020-10 | Num | 8 | BEST3. | 2020-10 | |
2020-11 | Num | 8 | BEST3. | 2020-11 | |
2020-12 | Num | 8 | BEST3. | 2020-12 | |
2021-01 | Num | 8 | BEST5. | 2021-01 | |
2021-02 | Num | 8 | BEST5. | 2021-02 | |
AanvragerType | Char | 12 | $12. | $12. | AanvragerType |
Bedrag(ODV/Trial) | Char | 23 | $23. | $23. | Bedrag(ODV/Trial) |
CodeAanvrager | Char | 14 | $14. | $14. | CodeAanvrager |
Kostenplaats | Char | 14 | $14. | $14. | Kostenplaats |
Requestcode | Char | 21 | $21. | $21. | Requestcode |
Tarief | Char | 12 | $12. | $12. | Tarief |
These are invalid variable names, since they start with a digit. Did you import them from an excel sheet?
- Cheers -
Hi,
With your variable names fixed, this is how I'd expect your dataset to be.
And in your request you specify 2021 but your code only adds up to 2020.
%MACRO doit();
DATA have;
length count 8;
%let count=0;
%do i=14 %to 21;
%do j=1 %to 12;
_20%sysfunc(putN(&i,z2))_%sysfunc(putN(&j,z2))=1;
%let count=%eval(&count.+1);
%end;
%end;
count=&count;
output;
RUN;
%MEND doit;
%doit();
DATA want;
length Eerder_2021 8;
set have;
Eerder_2021 = (Sum(of _201:)+sum(of _2020:));
RUN;
- Cheers -
Hello @rgjpot,
You will need to use the notation of SAS name literals:
Eerder_2021 = sum(of '201'n:, of '2020'n:);
My suggestion is that you rename your variables with valid variables names before any further processing. (_2020_01 instead of 2020-01)
Based on the results of your proc contents this is how I would do it here:
DATA have1;
set GD.GLIMS_DATA_2021_MERGE;
RUN;
DATA _NULL_;
set sashelp.vcolumn(keep=libname memname name) end=last;
where libname eq 'WORK'
and memname eq 'HAVE1'
and prxmatch('/^[0-9]/',name);
length newname $32;
newname=cats('_',translate(name,'_','-'));
if _N_ eq 1 then call execute('DATA HAVE2; SET HAVE1; rename ');
call execute("'"||strip(name)||"'n="||strip(newname));
if last then call execute(';RUN;');
RUN;
DATA want;
length Eerder_2021 8;
set HAVE2;
Eerder_2021 = (Sum(of _201:)+sum(of _2020:));
RUN;
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.