04-17-2017 10:39 PM
04-17-2017 11:12 PM
Difficult to address what you say your problem is, as your code has a number of mistakes AND, while you want sums, you don't include the sum function. The following does what your code looks like it should be doing. The sum function ignores missing values:
Data a; Input name $ amount; Datalines; Canada1 12.30 Canada2 45.90 Canada . France1 35.50 France2 55.50 France . ; Run; Data _null_; Set a; If name='Canada1' then do; Call Symput('x1',name); Call Symput('y1',amount); End; If name='Canada2' then do; Call Symput('x2',name); Call Symput('y2',amount); End; If name='France1' then do; Call Symput('m1',name); Call Symput('n1',amount); End; If name='France2' then do; Call Symput('m2',name); Call Symput('n2',amount); End; Run; Data b; Set a; If name= 'Canada' then do; Total_amunt=sum(&y1,&y2); End; Else if name='France' then do; Total_amunt=sum(&n1,&n2); End; run;
Art, CEO, AnalystFinder.com
04-18-2017 12:56 AM
I am not sure if you are using macro variables because they are needed for a larger problem or because you believe that they are needed here. If the objective is to 'calculate the total' then they are not needed (and would be impractical if there are many more rows per country). The following approach calculates a total, assuming the last line (amount = .) for each country is the line to receive the total.
Data have; Input name $ amount; Datalines; Canada1 12.30 Canada2 45.90 Canada . France1 35.50 France2 55.50 France . Run; data want(keep=name amount); set have; total+amount; if amount=. then do; amount=total; output want; total=0; end; run;
In this variation no macro variables are used and the final data set only has the country totals.
04-18-2017 03:48 AM
Thanks for your suggestions.
I am elaborating my question in detail level. I have 2 datasets,Names are final and detail.
Here Final dataset structure is fixed but detail table structure varies based on input data. So I needs to update the
Final dataset based on details dataset and if any values not availbale then keep it as NULL in final dataset.
FINAL DatasetTemplate-Fixed structure)
Detail dataset structurestructure is not fixed and below one is example).
Here only for India, code is same 1212 for all categories but for other countries code is different for each category.
Please help me to achieve above one. Thanks in advance.
04-18-2017 04:23 AM
I assume the number of detail lines in the final datasets may also vary from country to country. I f you want to have the total line before the detail lines, you can use Proc REPORT to calculate this for you. See example below how it could be done.
/* prepare some fake data */ Data a; Input name $ amount; name2 = substr(name, 1, anydigit(name) - 1); Datalines; Canada1 12.30 Canada2 45.90 France1 35.50 France2 55.50 India1 . ; /* * calculate the summary line before the detail lines * write ou the report data to a SAS dta set */ /*ods select all;*/ proc report data=a out=want ; column name2 name amount; define name2 / order; define name / order; define amount / analysis sum format=comma14.2; break before name2 / summarize; run; /* prepare the data to what we need */ data want2; length name $ 32; set want; if lowcase(_break_) = "name2" then do; name = catx("_", "Total", name2); end; keep name amount; run; proc print data=want2; run;
04-18-2017 04:53 AM
04-18-2017 05:21 AM
You can still use the technique I described with Proc REPORT, just ensure to only select detail rows which also appear in the final structure, then use a left join to combine the final structure with the result created by Proc REPORT, for the final structure data set you should add some seqnr to the rows, so that you can keep the sorting order of the final structure.
04-22-2017 10:48 PM
The sum function can handle blanks, but not non existing macro variables.
What happens if you insist on the macro variables existing? Declare them locally or globably, as appropriate is one method to do this. That's probably the quickest solution, IMO.